New to SQL Stored procedure - need help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

New to SQL Stored procedure - need help

Author

Topic

mmalaka
Starting Member

33 Posts

Posted - 2008-06-19 : 04:58:39

Experts

I am working on creating a Stored Procedure to do the following:
1- Read a table to get a SearchName field value
2- Using the SearchName read a table to get the columns returnd by the required Search
3- merge the columns into a string variable
4- use that string to create a stored procedure for that Search

I started by this:


declare @RowCnt int
declare @MaxRows int
declare @ColumnCnt int
declare @MaxColRows int
declare @ColText text

select @RowCnt = 1
select @ColumnCnt = 3
declare @TempTable table (rownum int IDENTITY (1, 1) Primary key NOT NULL ,SearchName varchar(100),TableName varchar(100))
insert into @TempTable (SearchName,TableName) select SearchName,TableName from SearchDetails 

select @MaxRows=count(*) from @TempTable

while @RowCnt <= @MaxRows
begin

declare @ColumnTable table (rownum int IDENTITY (1, 1) Primary key NOT NULL ,ColumnOrigin varchar(100),ColumnAlias varchar(100))
insert into @ColumnTable (ColumnOrigin,ColumnAlias) select ColumnOrigin,ColumnAlias from IMGSColumnsAlias Order by ColumnPosition ASC

select @MaxColRows=count(*) from @ColumnTable


while @ColumnCnt <= @MaxColRows
begin




Select @ColumnCnt = @ColumnCnt + 1
end

Select @RowCnt = @RowCnt + 1
end

Any advice?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 05:08:35

Whats the field in IMGSColumnsAlias against which you want to serach for values in SearchName?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 05:11:25

For every loop you always do the same insert?

select ColumnOrigin,ColumnAlias from IMGSColumnsAlias Order by ColumnPosition ASC

where is the WHERE clause?

E 12°55'05.25"
N 56°04'39.16"

mmalaka
Starting Member

33 Posts

Posted - 2008-06-19 : 05:21:48

sorry I think I posted the wrong code


declare @RowCnt int
declare @MaxRows int
declare @ColumnCnt int
declare @MaxColRows int
declare @ColText text
declare @SearcName varchar(100) 

select @RowCnt = 1
select @ColumnCnt = 3
declare @TempTable table (rownum int IDENTITY (1, 1) Primary key NOT NULL ,SearchName varchar(100),TableName varchar(100))
insert into @TempTable (SearchName,TableName) select SearchName,TableName from SearchDetails 

select @MaxRows=count(*) from @TempTable

while @RowCnt <= @MaxRows
begin

select @SearcName=select SearchName  from @TempTable where rownum = @RowCnt 


declare @ColumnTable table (rownum int IDENTITY (1, 1) Primary key NOT NULL ,ColumnOrigin varchar(100),ColumnAlias varchar(100))
insert into @ColumnTable (ColumnOrigin,ColumnAlias) select ColumnOrigin,ColumnAlias from IMGSColumnsAlias where SearchName = @SearcName Order by ColumnPosition ASC
select @MaxColRows=count(*) from @ColumnTable


while @ColumnCnt <= @MaxColRows
begin




Select @ColumnCnt = @ColumnCnt + 1
end

Select @RowCnt = @RowCnt + 1
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 05:46:00

Wont this be enough?

declare @ColumnTable table (rownum int IDENTITY (1, 1) Primary key NOT NULL ,ColumnOrigin varchar(100),ColumnAlias varchar(100))
insert into @ColumnTable (ColumnOrigin,ColumnAlias)
select ColumnOrigin,ColumnAlias 
from IMGSColumnsAlias  i
INNER JOIN SearchDetails s
ON s.SearchName=i.SearchName

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 05:49:37

[code]SELECT ColumnOrigin,
ColumnAlias,
SearchName,
TableName
FROM (
SELECT ca.ColumnOrigin,
ca.ColumnAlias,
ROW_NUMBER() OVER (PARTITION BY sd.SearchName, sd.TableName ORDER BY ca.ColumnPosition) AS RecID,
sd.SearchName,
sd.TableName
FROM IMGSColumnsAlias AS ca
INNER JOIN SearchDetails AS sd ON sd.SearchName = ca.SearchName
) AS d
WHERE RecID BETWEEN 1 AND 3
ORDER BY TableName,
SearchName,
RecID[/code]

E 12°55'05.25"
N 56°04'39.16"

mmalaka
Starting Member

33 Posts

Posted - 2008-06-19 : 05:51:55

quote:

Originally posted by visakh16

Wont this be enough?

declare @ColumnTable table (rownum int IDENTITY (1, 1) Primary key NOT NULL ,ColumnOrigin varchar(100),ColumnAlias varchar(100))
insert into @ColumnTable (ColumnOrigin,ColumnAlias)
select ColumnOrigin,ColumnAlias 
from IMGSColumnsAlias  i
INNER JOIN SearchDetails s
ON s.SearchName=i.SearchName

ok so this instead of using the Where

but now I need to read the values of the ColumOrigion and ColumnAlias to generate a string statment like this one

set @ColText = @ColText + ''+ @ColumAlias + ' + cast('+@ColumName+' as Char + '

remmber that each search would have more than one column and so I am trying to use

select @MaxColRows=count(*) from @ColumnTable

 
while @ColumnCnt <= @MaxColRows
begin

select @ColumName= ColumnOrigin  from @ColumnTable where @ColumnCnt = @RowCnt 
select @ColumAlias= ColumnAlias  from @ColumnTable where @ColumnCnt = @RowCnt 


set @ColText = @ColText + '<b>'+ @ColumAlias + '</b><br/> + cast('+@ColumName+' as Char + <br/>'


Select @ColumnCnt = @ColumnCnt + 1
end

mmalaka
Starting Member

33 Posts

Posted - 2008-06-19 : 05:54:10

quote:

Originally posted by Peso

SELECT		ColumnOrigin,
		ColumnAlias,
		SearchName,
		TableName
FROM		(
			SELECT		ca.ColumnOrigin,
					ca.ColumnAlias,
					ROW_NUMBER() OVER (PARTITION BY sd.SearchName, sd.TableName ORDER BY ca.ColumnPosition) AS RecID,
					sd.SearchName,
					sd.TableName
			FROM		IMGSColumnsAlias AS ca
			INNER JOIN	SearchDetails AS sd ON sd.SearchName = ca.SearchName
		) AS d
WHERE		RecID BETWEEN 1 AND 3
ORDER BY	TableName,
		SearchName,
		RecID

E 12°55'05.25"
N 56°04'39.16"

Can you plz explan ur code to me? I am totally new to this

Why RecID BETWEEN 1 AND 3???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 06:06:50

quote:
Originally posted by mmalaka

quote:
Originally posted by visakh16

Wont this be enough?

declare @ColumnTable table (rownum int IDENTITY (1, 1) Primary key NOT NULL ,ColumnOrigin varchar(100),ColumnAlias varchar(100))
insert into @ColumnTable (ColumnOrigin,ColumnAlias)
select ColumnOrigin,ColumnAlias
from IMGSColumnsAlias i
INNER JOIN SearchDetails s
ON s.SearchName=i.SearchName

ok so this instead of using the Where

but now I need to read the values of the ColumOrigion and ColumnAlias to generate a string statment like this one

set @ColText = @ColText + ''+ @ColumAlias + ' + cast('+@ColumName+' as Char + '

remmber that each search would have more than one column and so I am trying to use

select @MaxColRows=count(*) from @ColumnTable

while @ColumnCnt <= @MaxColRows
begin

select @ColumName= ColumnOrigin from @ColumnTable where @ColumnCnt = @RowCnt
select @ColumAlias= ColumnAlias from @ColumnTable where @ColumnCnt = @RowCnt

set @ColText = @ColText + ''+ @ColumAlias + ' + cast('+@ColumName+' as Char + '

Select @ColumnCnt = @ColumnCnt + 1
end

The code i've posted inserts all records into @ColumnTable from IMGSColumnsAlias having SearchNmae in other table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources