doesn't quotename work inside of exec?
I'm trying to do something simi开发者_如何转开发lar to this question. The answer recommends using quotename inside of an exec.
Similar to this:
declare @var nvarchar(128)
set @var = 'hello world'
exec('print ''' + quotename(@var) + '''')
But this doesn't work (Incorrect syntax near 'quotename'). Is the answer wrong?
I know I can generate the string first, put it in a variable then use it with exec sp_executeSql
, but I would rather do it the way in the question if it can work...
Best solution I could come up with for you is to pre-quote the value:
declare @var nvarchar(128);
set @var = 'hello world';
declare @quoted varchar(100);
select @quoted = quotename(@var);
exec('print ''' + @quoted + '''');
From the Transact-SQL Reference:
Execute a character string
{ EXEC | EXECUTE }
( { @string_variable | [ N ]'tsql_string' } [ + ...n ] )
[ AS { LOGIN | USER } ='name' ]
[;]
The syntax specification only allows string variables and string literals in this situation. Doesn't make sense, but calling functions returning strings is not allowed here.
精彩评论