I have a dynamic query (that works) in a stored procedure. It gets N records starting at record number X from a larger set of records. I now need to return the count of the larger set of records to the calling application, and I don't want to have to create another stored proc to do it.
I defined the
parameter in the store proc's prototype, but I haven't been able to find a way to get the count into the parameter. It keeps telling me I have to define the scalar parameter
, but it IS already defined.
My query looks something like this"
The stored procedure looks something like this:
sp_mySP( parameters..., @totalRecords int OUT)
-- the query string essentially looks like this (comments added for clarity):
set @queryStr =
-- drop temptable if it exists
+ 'if #temptable exists drop #temptable; '
-- gets the data specified by parameters
+ 'select * into #temptable from mytable where blah blah; '
-- dets the data in the specified rows
+ 'select * from #temptable where blah blah; '
-- counts the records in #temptable and stores them in out parameter
+ ' set @totalRecords = (select count(*) from #temptable); '
-- at this point, I call
How do I set
inside a dynamic query?
I've also tried this:
Change the last part of the string to this:
+ ' set @outCount = (select count(*) from #temptable); '
and then did this:
declare @outCounter int
exec sp_executesql @queryStr, N'@outCount int OUTPUT', @outCounter OUPUT
select @outCounter as counter
This results in outCounter being set to