Must declare the scalar variable

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 2005 Forums

Transact-SQL (2005)

Must declare the scalar variable

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2007-06-26 : 08:59:57

I get the following error when I try to run this:

Must declare the scalar variable "@xID". I can't figure out why. Any help to figure this thing is appreciate.

thanks

DECLARE @xCount Int
DECLARE @xNum Int
DECLARE @xDBName Varchar(255)
DECLARE @xSQL NVARCHAR(4000)
DECLARE @xID INT

DECLARE @cList Table( DBID INT NOT NULL IDENTITY(1, 1),
col1 Varchar(256)
)

Insert Into @cList
Select col1 from tbl1
Set @xNum = @@RowCount

Set @xCount = 1
SET @xID=74624

While @xCount < @xNum

BEGIN
SET @SQL = (Select col1 from @cList Where DBID = @xCount)
SET @SQL = @SQL + ' AND ( @xID = cID or @xID = 0 )'

EXEC sp_executesql @SQL

Set @xCount = @xCount + 1

END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-26 : 09:02:29

Note sure what you are trying to do
Make sure you read this fully
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-26 : 09:54:22

The dynamic SQL is outside the scope of the @xID variable declaration.
You will need to concatenate the actual value of @xID into your SQL String. You cannot reference variables in your dynamic sql string that were declared outside of the dynamic statement. Dynamic SQL runs within its own scope.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources