?
Solved

Reuse sql statement with variables in one stored procedure

Posted on 2011-05-11
3
Medium Priority
?
218 Views
Last Modified: 2012-05-11
See the statement below.

set @query = 'insert into #ReportData
                        select * from
            (select ''' + @theheader + ''' as theHeader,''' + @rowType + ''' as rowtype,calcdate orderDate, ' + @theData + ' as calcdate from vinstatsnapshot
                  where calcdate >= ''' + convert(varchar,@fromdate,101) + ''' and calcdate <= ''' + convert(varchar,@todate,101) + ''' and storeno = ' + cast(@storeno as varchar) + ' and branchno = ' + cast(@branch as varchar) + '
            ) t1
            pivot
            (max(calcdate) for orderDate in ('+@listcol+')
            ) as p
            '
execute (@query)

I run this query for each row that I am reporting on.  The variables @theheader, @rowtype and @thedata changes many times. I am currently setting the variables ahead of the statement then running the statement.  

What I want to do is set the variables call the statement without having to copy paste the statement every time I change the variables.  I want to set the variables, call the statement etc. etc..
0
Comment
Question by:rquirion
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35737910
you cannot, unless you create a procedure that accepts the parameters, and runs the code then...

the issue is that once you set @query to a value, it will not change based on the variables used to do the concat ...
0
 
LVL 7

Accepted Solution

by:
Cboudroz earned 2000 total points
ID: 35739994
not sure in perfectly understood but can u not just used a loop and a cursor?

let me know, if I'm missing something!



declare cur cursor 
for 
	select 
		variable1
		, variable2
	from 
		tablewhitvariable

open cur

fetch cur into @newvariablevalue1, @newvariablevalue2

while @@FETCH_STATUS = 0
begin

	set @theheader = @newvariablevalue1
	set @rowType = @newvariablevalue2
	
	set @query = 'insert into #ReportData 
							select * from
				(select ''' + @theheader + ''' as theHeader,''' + @rowType + ''' as rowtype,calcdate orderDate, ' + @theData + ' as calcdate from vinstatsnapshot 
					  where calcdate >= ''' + convert(varchar,@fromdate,101) + ''' and calcdate <= ''' + convert(varchar,@todate,101) + ''' and storeno = ' + cast(@storeno as varchar) + ' and branchno = ' + cast(@branch as varchar) + '
				) t1
				pivot
				(max(calcdate) for orderDate in ('+@listcol+')
				) as p
				'
	execute (@query)

		

	-- grap next variable
	fetch cur into @newvariablevalue1, @newvariablevalue2

end

close cur
deallocate cur

Open in new window

0
 

Author Closing Comment

by:rquirion
ID: 35745252
This solution is perfect!  Thank you
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question