Click here to Skip to main content
12,300,607 members (23,305 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-Server
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:

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:

+ ' 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 NULL
Posted 2-Jun-11 4:49am
Edited 2-Jun-11 5:47am
v4
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

I ended up doing this:


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
  Permalink  
v2
Comments
TweakBird 2-Jun-11 14:31pm
   
Good Answer. My 5!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

i tried like this it worked for me


declare @cahr int
exec('select '+@cahr  + ' count(*) from dbo.Users')
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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

Dynamic 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
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160525.2 | Last Updated 2 Jun 2011
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100