Dynamic query and return value

Question

0.00/5 (No votes)

See more:

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 OUT 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 @totalRecords, but it IS already defined.

My query looks something like this"

The stored procedure looks something like this:

SQL

sp_mySP( parameters..., @totalRecords int OUT)
as
begin
    -- 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 
    exec (@queryStr)
end

How do I set @totalRecords inside a dynamic query?

EDIT ===============

I've also tried this:

Change the last part of the string to this:

SQL

+ ' set @outCount = (select count(*) from #temptable); '

and then did this:

SQL

declare @outCounter int
exec sp_executesql @queryStr, N'@outCount int OUTPUT', @outCounter OUPUT
select @outCounter as counter

This results in outCounter being set to NULL

Posted 2-Jun-11 4:49am

#realJSOP

Updated 2-Jun-11 5:47am

v4

Add a Solution

3 solutions

Solution 2

i tried like this it worked for me

SQL

declare @cahr int
exec('select '+@cahr  + ' count(*) from dbo.Users')

Posted 2-Jun-11 6:18am

Steven.Pinto2000

Solution 1

This article I have found to be a good reference. The example below is taken from it

Dynamic SQL[^]

SQL

SELECT @sql =
  N' SELECT @cnt = COUNT(*) FROM dbo.' + quotename(@tbl) +
  N' WHERE LastUpdated BETWEEN @fromdate AND ' +
  N'                           coalesce(@todate, ''99991231'')'
  SELECT @params = N'@fromdate datetime, ' +
                   N'@todate   datetime = NULL, ' +
                   N'@cnt      int      OUTPUT'
  EXEC sp_executesql @sql, @params, '20060101', @cnt = @count OUTPUT

Posted 2-Jun-11 6:13am

Simon_Whale

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Accepted Answer · 2011-06-02T06:18:00

I ended up doing this:

SQL

sp_mySP( parameters..., @totalRecords int OUT)
as
begin
    -- 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
    -- I changed the variable here from the sp's out parameter to @outCount <<--------
    + ' set @outCount = (select count(*) from #temptable); '
 
    -- at this point, I call 
    declare @outCounter Int

    -- and change this line to be this: <<--------------
    execute (sp_executesql @queryStr, N'outCount int OUTPUT', @outCounter OUTPUT)

    -- and finally, I did this: <<--------------
    set @totalrecords = @outCounter
end

Dynamic query and return value

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Dynamic query and return value

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us