存储过程变量申明 Must declare the scalar variable "@var1".

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    November 20th, 2006, 06:28 AM

r937 SQL Consultant   Join Date: Feb 2003 Location: Toronto Canada Posts: 25,352     Time spent in forums: 3 Months 4 Days 14 h 14 m 27 sec  Reputation Power: 3899

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) __________________ r937.com |  rudy.ca please visit  Simply SQL and buy my book

# 3    November 20th, 2006, 06:51 AM

mcal1 Registered User   Join Date: Nov 2006 Posts: 2     Time spent in forums: 19 m 53 sec  Reputation 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

# 4    November 20th, 2006, 06:57 AM

r937 SQL Consultant   Join Date: Feb 2003 Location: Toronto Canada Posts: 25,352     Time spent in forums: 3 Months 4 Days 14 h 14 m 27 sec  Reputation Power: 3899

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    November 22nd, 2006, 11:15 AM

BillyDunny Contributing User   Join Date: Sep 2003 Posts: 508     Time spent in forums: 4 Days 8 h 28 m 55 sec  Reputation Power: 15

Quote: 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: Original - 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