asked on

Reuse sql statement with variables in one stored procedure

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..

Guy Hengel [angelIII / a3]

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 ...

ASKER CERTIFIED SOLUTION

Cboudroz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rquirion

ASKER

This solution is perfect! Thank you