locked
Must declare the scalar variable "@recCount". RRS feed

  • Question

  • User1305247985 posted

    Hi,

    Can somone please tell me why I get the  error 'Must declare the scalar variable "@recCount".' when I execute this Store Procedure?

     

    ALTER PROCEDURE dbo.CountTEst

     AS

       SET NOCOUNT ON
        DECLARE @sql as nvarchar(100);
        DECLARE @recCount as int

     SET @SQL = 'SELECT @recCount = COUNT(*)
       FROM     Pool'
       exec sp_executesql @SQL
       RETURN @recCount

     

    In this case I expect RETURN value = 4.

    I've tried various approaches, including adding an OUTPUT parameter to the exec statement, but just can't get the syntax correct.

    Thanks
     

     

    Tuesday, August 21, 2007 8:30 AM

Answers

  • User1305247985 posted

     hi Diamsorn, didn't see your solution before finding mine  on http://www.sommarskog.se/dynamic_sql.html.

    both are good, thanks for your help/time, much appreciated. 

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 21, 2007 1:40 PM

All replies

  • User56700650 posted

    If you're using sql your syntax is slightly wrong.  There should not an "as" in your variable declarations.
    Also you may want to set your variables in separate statements:

    SELECT @recCount = COUNT(*)
    FROM Pool

    Then set @Sql to it's value

    Tuesday, August 21, 2007 8:40 AM
  • User-1052551871 posted

    Try this

     ALTER PROCEDURE dbo.CountTEst

     AS

    DECLARE @recCount int

    SET @recCount = (SELECT COUNT(*) FROM Pool)

    select @recCount

    Tuesday, August 21, 2007 8:47 AM
  • User1305247985 posted

     Hi,

    thanks for the quick responses, however the example I posted is just a simple version of a more complex stored procedure in which I have to build the SQL statement depending on input parameters.

    Therefore I need to use the SET @SQL = .. statement, which in turn means I need to do the exe sp_exelcutesql.

    This syntax is where I'm confused. 

    Tuesday, August 21, 2007 9:38 AM
  • User897471407 posted

    The reason for the error is because your declareing the variabled @rowcount oustide of the scope of your sql statement.

    Because your using Dynamic SQL when the SQL is executed the, the SQL engine doesn't realize the variable has been declared.

    What I would do is to build a temporary table inside your stored procedure, build your dynamic SQL and insert into your temporary table.  Then select from your temp table to populate the variables you need returned.

    Tuesday, August 21, 2007 9:50 AM
  • User1305247985 posted

    Hi Diamsorn,

    Your solution seems like a lot of work just to return an output Count.

    See below:

    ----- 

    ALTER PROCEDURE dbo.CountTEst

     AS

       SET NOCOUNT ON
        DECLARE @sql nvarchar(100);
        DECLARE @recCount int    


    SET @SQL = 'SELECT COUNT(*)
       FROM     Pool'
     
    exec sp_executesql @SQL

     -----

     

    When executed, results are:

    Running [dbo].[CountTEst].

    Column1    
    -----------
    4          
    No rows affected.
    (1 row(s) returned)
    @RETURN_VALUE = 0
    Finished running [dbo].[CountTEst].
     

    So I get the correct Count value here, but it should just be a matter of (somehow) setting my Return value or an OUTPUT parameter to = 4

    Tuesday, August 21, 2007 10:32 AM
  • User897471407 posted

    your right it is alot of work.  The reson why yours works in your 2nd example is because your not setting it to a parameter.  However in your original question you were.  When you execute a SQL statement as you are the parameters are local to that scope alone.  So your declaration of your output is outside of that scope.

    You stated that you are building a fairly large sql statement dependent upon other parameters.  Thus giving me the impression that you may be setting more output parameters in your dynamic SQL statement,  Which is why I suggested to go with building a temporary table.

     

    Tuesday, August 21, 2007 11:06 AM
  • User1305247985 posted

    Hi,

    I understand what you're saying that it's not recognising the @recCount parameter in the first sql statement.  My my second example I just wanted to show that it worked without using the parameter in the select statement, but was hoping that I could somehow directly set an output parameter return value of 4, in the exec statement.

    In my own more complex example I only use input parameters to determine how I build the WHERE.. clause in my SELECT statement. I excluded these in my example here because I didn't think it was relevant.

    So the only output I need is the record count.

     

    Tuesday, August 21, 2007 11:47 AM
  • User897471407 posted

    I'm not sure if this will work, and unfortunatly can't test it at the moment.  But lets try declaring the parameter in your dynamic SQL

    Set @SQL = 'DECLARE @rowCount int; SELECT @rowCount = Count(*) WHERE....'

    see what that gives us.

     

    Tuesday, August 21, 2007 11:52 AM
  • User1305247985 posted

    I tried this:

    ALTER PROCEDURE dbo.CountTEst

    AS

       SET NOCOUNT ON
        DECLARE @sql nvarchar(100);
        DECLARE @recCount int;  

    SET @sql = 'DECLARE @recCount int; SELECT @recCount = COUNT(*)
       FROM     Pool'
     
    exec sp_executesql @sql

    Return @recCount

     

    Result:

    Running [dbo].[CountTEst].

    The 'CountTEst' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
    No rows affected.
    (0 row(s) returned)
    @RETURN_VALUE = 0
    Finished running [dbo].[CountTEst].

     

    then I tried this, which I found elsewhere:

    ALTER PROCEDURE dbo.CountTEst

    AS

       SET NOCOUNT ON
        DECLARE @sql nvarchar(100);
        DECLARE @recCount int; 
        DECLARE @parms nvarchar(100);

    SET @sql = 'SELECT @recCount = COUNT(*)
                FROM Pool '
    SET @parms = '@recCount int OUTPUT'           
     
    exec sp_executesql @sql, @parms

    Return @recCount

    Result:

    Running [dbo].[CountTEst].

    Parameterized Query '(@recCount int OUTPUT)SELECT @recCount = COUNT(*)
                FROM Pool ' expects parameter @recCount, which was not supplied.
    The 'CountTEst' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
    No rows affected.
    (0 row(s) returned)
    @RETURN_VALUE = 0
    Finished running [dbo].[CountTEst].
     

     
    then I tried this:

    ALTER PROCEDURE dbo.CountTEst

    @recCount1 int OUTPUT

    AS

       SET NOCOUNT ON
        DECLARE @sql nvarchar(100);
        DECLARE @recCount int;
        DECLARE @parms nvarchar(100);

    SET @sql = 'SELECT @recCount = COUNT(*)
                FROM Pool '
    SET @parms = '@recCount1 int OUTPUT'          
     
    exec sp_executesql @sql, @parms

    Return @recCount

    When I executed this I entered a Null value for @recCount1

    Result:

    Running [dbo].[CountTEst] ( @recCount1 = <NULL> ).

    Must declare the scalar variable "@recCount".
    The 'CountTEst' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
    No rows affected.
    (0 row(s) returned)
    @recCount1 = <NULL>
    @RETURN_VALUE = 0
    Finished running [dbo].[CountTEst].

    I'm stumped, and it's probably some niggly syntax issue, any more ideas please?

     

    Tuesday, August 21, 2007 12:34 PM
  • User897471407 posted
    <DIR>
    Got it, and I was able to test this as well. 
     
    DECLARE @sql nvarchar(1000)
    Declare @recCount int
    SET @sql = 'SET @Count = (SELECT COUNT(*) FROM pool);'
    
    --PRINT @sql
    exec sp_executesql @sql, N'@Count int output', @Count = @recCount OUTPUT
    
    select @recCount As RecordCount
    
     </DIR>
    Tuesday, August 21, 2007 1:26 PM
  • User1305247985 posted

     I've discovered the solution....yipeeeeee. see below:

    ALTER PROCEDURE dbo.CountTEst

    AS

       SET NOCOUNT ON
        DECLARE @sql nvarchar(100);
        DECLARE @recCount int; 
        DECLARE @parms nvarchar(100);

    SET @sql = 'SELECT @recCount1 = COUNT(*)
                FROM Pool '
    SET @parms = '@recCount1 int OUTPUT'           
     
    exec sp_executesql @sql, @parms, @recCount1 = @recCount OUTPUT

    Return @recCount

    Tuesday, August 21, 2007 1:33 PM
  • User1305247985 posted

     hi Diamsorn, didn't see your solution before finding mine  on http://www.sommarskog.se/dynamic_sql.html.

    both are good, thanks for your help/time, much appreciated. 

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 21, 2007 1:40 PM
  • User897471407 posted

    your welcome.

     

    Please remember to mark an answer. Doing so helps with searching for other issues like yours.

    Tuesday, August 21, 2007 1:43 PM
  • User203683542 posted

    The issue happens since the syntax for using output parameters with sp_executesql is different.

    For reference and examples check out:

    http://support.microsoft.com/kb/262499

    Tuesday, November 1, 2011 7:21 PM