Must declare the scalar variable "@Cnt".

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Must declare the scalar variable "@Cnt".

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-03-06 : 01:11:05

Dear All,

I have simple skeleton test as below, i have declare @Cnt but keep raised me error: Must declare the scalar variable "@Cnt".

Please Advise.

declare @Cnt int
declare @AID varchar(max)
DECLARE @SQLString varchar(8000)

set @AID='2,3'

SET @SQLString = N'select @Cnt=count(*) from tblG a '
SET @SQLString = @SQLString + 'where EXISTS(select AID from tblM b where a.AID = b.AID and AID in (' + @AID +'))';
--print @SQLString;
EXEC (@SQLString);
if(@Cnt>0)
begin
print 'aaa'
end
else
:

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-06 : 01:21:53

you don't need dynamic SQL for this


select @Cnt = count(*)
from   tblG a
where  exists (select * from tblM b where b.AID = a.AID and ',' + b.AID + ',' like '%,' + @AID + ',%')

please refer to http://www.sommarskog.se/arrays-in-sql.html for further information on how to handle such case

KH
[spoiler]Time is always against us[/spoiler]

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-03-06 : 01:25:06

I got it. missing ''

Thank you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-06 : 01:26:07

quote:
Originally posted by micnie_2020

Dear All,

I have simple skeleton test as below, i have declare @Cnt but keep raised me error: Must declare the scalar variable "@Cnt".

Please Advise.

declare @Cnt int
declare @AID varchar(max)
DECLARE @SQLString varchar(8000)

set @AID='2,3'

SET @SQLString = N'select @Cnt=count(*) from tblG a '
SET @SQLString = @SQLString + 'where EXISTS(select AID from tblM b where a.AID = b.AID and AID in (' + @AID +'))';
--print @SQLString;
EXEC (@SQLString);
if(@Cnt>0)
begin
print 'aaa'
end
else
:

The problem with this query is when you use exec(), it is executing in another context and @cnt is not defined there.

If you want to use dynamic sql, you should use sp_executesql and pass in the parameter


declare @Cnt int
exec sp_executesql @SQLString, N'@Cnt int OUTPUT', @Cnt OUTPUT
select @Cnt

read this also
http://www.sommarskog.se/dynamic_sql.html

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources