http://forums.devshed.com/ms-sql-development-95/stored-procedure-must-declare-the-scalar-variable-var1t-403571.html
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
| ||||
| ||||
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) |
#
3
| |||
| |||
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 |
#
4
| ||||
| ||||
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" |
#
5
| |||
| |||
Quote:
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:
|