Click here to Skip to main content
Rate this: bad
good
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 5:49am
Edited 2-Jun-11 6: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 at 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
0 OriginalGriff 280
1 Sergey Alexandrovich Kryukov 279
2 CPallini 205
3 Maciej Los 197
4 Afzaal Ahmad Zeeshan 160
0 OriginalGriff 5,635
1 DamithSL 4,496
2 Maciej Los 3,942
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,180


Advertise | Privacy | Mobile
Web04 | 2.8.141216.1 | Last Updated 2 Jun 2011
Copyright © CodeProject, 1999-2014
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