Reuse sql statement with variables in one stored procedure
Posted on 2011-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) + '
(max(calcdate) for orderDate in ('+@listcol+')
) as p
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..