存储过程变量申明 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 ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[admin3c]-- Add the parameters for the stored procedure here@tableName varchar(100),@authorityId varchar(50)ASBEGINSET NOCOUNT ON;Declare @SQL VarChar(2000),@var1 varchar(50),@var2 int,@total intSELECT @SQL = 'select@var1 = name,@var2 = sum(age),@total = count(*)from dbo.'+@tableName+' where authority='+@authorityId+'group by name'Exec ( @SQL)select @total - @var2END

---

  #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 intselect@var1 = something,@var2 = sum(case when something != '12' then 1 else 0 end),@total = count(*)from sometablewhere something = 1111group by somethingselect @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