#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    2
    Rep Power
    0

    Stored procedure - Must declare the scalar variable "@var1".


    Hi all, when i run this query i get the following error "Must declare the scalar variable "@var1". What am i doing wrong?

    Code:
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[admin3c]
    	-- Add the parameters for the stored procedure here
    	@tableName varchar(100),
    	@authorityId varchar(50)
    
    AS
    BEGIN
    	SET NOCOUNT ON;
    
    Declare @SQL VarChar(2000),
    @var1 varchar(50),
    @var2 int,
    @total int
    
    SELECT @SQL = '
    select	@var1 = name,
    	@var2 = sum(age),
    	@total = count(*)
    from dbo.'+@tableName+' 
    where authority='+@authorityId+'
    group by name'
    Exec ( @SQL)
    
    select @total - @var2
    
    END
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,348
    Rep Power
    4281
    i'm not sure why you're getting that particular error message, however, there's still a problem in how you're approaching this --

    your SELECT will return one row per name, i.e. there will be more than one row in the results

    therefore, you cannot stuff the multiple values of name, sum(age), and count(*) into the scalar variables @var1, @var2, @total

    (at best, you'd manage to snag only the last ones)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    2
    Rep Power
    0
    Actually this query will only return one row. I modified the statement at little before i posted it here and probaly messed it up but its guareented to return only one row. Also the query works when i dont run it as an stored procedure.
    This works:

    Code:
    declare @var1 varchar(50),
    @var2 int,
    @total int
    
    select	@var1 = something,
    		@var2 = sum(case when something != '12' then 1 else 0 end),
    		@total = count(*)
    from sometable
    where something = 1111
    group by something
    
    select @total - @var2
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,348
    Rep Power
    4281
    if you're gonna write the WHERE clause with only one "something" then you shouldn't even have it in the query -- remove it from the SELECT, and remove the GROUP BY

    i still don't know why you're getting "Must declare the scalar variable "@var1"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2003
    Posts
    508
    Rep Power
    18
    Originally Posted by mcal1
    Hi all, when i run this query i get the following error "Must declare the scalar variable "@var1". What am i doing wrong?
    You're getting that error because of a scope issue. The variables @var1, @var2 etc are declared outside of dynamic SQL that you are executing. Read these articles on dynamic SQL.

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

    http://www.sqlteam.com/item.asp?ItemID=4619


    Try something like this:
    SQL Code:
    DECLARE 
    	@SQL nvarchar(2000), 
    	@var1 VARCHAR(50), 
    	@var2 INT, 
    	@total INT
     
    SELECT @SQL = '
    select 
    	@var1 = name,
    	@var2 = sum(age),
    	@total = count(*)
    from dbo.'+@tablename+' 
    where authority='+@authorityid+'
    group by name'
     
     
    EXEC sp_executesql @SQL, 
    	N'@var1 varchar(50) output, @var2 int output, @total int output', 
    	@var1 output, @var2 output, @total output
     
     
    SELECT @total - @var2
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2014
    Posts
    1
    Rep Power
    0

    thank you


    Originally Posted by BillyDunny
    You're getting that error because of a scope issue. The variables @var1, @var2 etc are declared outside of dynamic SQL that you are executing. Read these articles on dynamic SQL.

    How to specify output parameters when you use the sp_executesql stored procedure in SQL Server

    http://www.sqlteam.com/item.asp?ItemID=4619


    Try something like this:
    SQL Code:
    DECLARE 
    	@SQL nvarchar(2000), 
    	@var1 VARCHAR(50), 
    	@var2 INT, 
    	@total INT
     
    SELECT @SQL = '
    select 
    	@var1 = name,
    	@var2 = sum(age),
    	@total = count(*)
    from dbo.'+@tablename+' 
    where authority='+@authorityid+'
    group by name'
     
     
    EXEC sp_executesql @SQL, 
    	N'@var1 varchar(50) output, @var2 int output, @total int output', 
    	@var1 output, @var2 output, @total output
     
     
    SELECT @total - @var2
    I know this thread is old but I have been banging my head against a wall trying to figure out this same error and thanks to your post it's working now. Thank you so very much!

IMN logo majestic logo threadwatch logo seochat tools logo