sp_executeSQL Command ERROR!!!!

[sanjdhiman]

Hi

I have the following loop to execute a command in a temp table
Code:
------------------------------------------------

----------------------------------------------
-- START LOOP
-----------------------------------------------
WHILE @Counter <> @MaxRow
BEGIN
--Execute each Row
SELECT @SQL = ExecuteQuery
FROM #ToDistributeBondData
WHERE RowId = @Counter
sp_executesql @SQL
SELECT @Counter = @Counter + 1
END



Example data in the temp table is as follows:
------------------------------------------------
EXEC Update_Client_Entity_iu 10000, 410, '2006.07.27','11:09:28'

I know this is correct, because if I run this on its own in a Q.Analyser window it works fine.

However, if I run it in the loop I get this,

Error Msg:
------------------------------------------------
Server: Msg 170, Level 15, State 1, Line 38
Line 38: Incorrect syntax near 'sp_executesql'.


Much Appreciated

Sanj

 

[SQLDenis]

you need exec in front of it

--fails
declare @SQL nvarchar(99)
select @SQL ='select * from products'
sp_executesql @SQL

-correct
declare @SQL nvarchar(99)
select @SQL ='select * from products'
exec sp_executesql @SQL

if a proc is not the only statement in a batch then you need to use exec in front of it

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/