July 19, 2011 at 1:19 am
Hi, here i have code
DECLARE @var INT =1
DECLARE @Sqltxt AS VARCHAR(100) = 'SELECT * FROM Production.Product WHERE ProductID = '+CAST(@var AS VARCHAR);
--this works fine
EXECUTE (@Sqltxt) ;
--this throws error
EXEC ('SELECT * FROM Production.Product WHERE ProductID ='+CAST(@var AS VARCHAR))
when i build dynamic Statement in place it variable and then execute it works fine.
and statement where i directly convert into Exec() part it throws error.
Do any one know why this happens?
July 19, 2011 at 3:29 am
Per the documentation it requires a constant string, a variable, or any combination concatenating those two options. It does not say that it supports expressions, i.e. returns from functions, built-in or otherwise:
From http://technet.microsoft.com/en-us/library/ms188332.aspx:
Execute a character string
{ EXEC | EXECUTE }
( { @string_variable | [ N ]'tsql_string' } [ + ...n ] )
[ AS { LOGIN | USER } = ' name ' ]
[;]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 19, 2011 at 11:22 pm
Hi
You should refrain from you that type of code as it's print to sql injection.
Rather using something like sp_executesql and variables.
You will also get better performance by using parameters as opposed to strings execution.
Cheers
Jannie
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply