Is it possible to issue CREATE statements using sp_executesql with parameters?

  • A+
Category:Languages

I'm trying to dynamically create triggers, but ran into a confusing issue around using sp_executesql and passing parameters into the dynamic SQL. The following simple test case works:

DECLARE @tableName sysname = 'MyTable'; DECLARE @sql nvarchar(max) = N'     CREATE TRIGGER TR_' + @tableName + N' ON ' + @tableName + N' FOR INSERT         AS         BEGIN             PRINT 1         END'; EXEC sp_executesql @sql 

However, I want to be able to use @tableName (and other values) as variables within the script, so I passed it along to the sp_executesql call:

DECLARE @tableName sysname = 'ContentItems'; DECLARE @sql nvarchar(max) = N'     CREATE TRIGGER TR_' + @tableName + N' ON ' + @tableName + N' FOR INSERT         AS         BEGIN             PRINT @tableName         END'; EXEC sp_executesql @sql, N'@tableName sysname', @tableName=@tableName 

When running the above, I get an error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'TRIGGER'.

After trying I few things, I've discovered that even if I don't use @tableName in the dynamic SQL at all, I still get this error. And I also get this error trying to create a PROCEDURE (except, obviously, the message is Incorrect syntax near the keyword 'PROCEDURE'.)

Since the SQL runs fine either directly or when not supplying parameters to sp_executesql, this seems like I'm running into a true limitation in the SQL engine, but I don't see it documented anywhere. Does anyone know if there is a way to accept to a dynamic CREATE script, or at least have insight into the underlying limitation that's being run into?

Update I can add a PRINT statement, and get the below SQL, which is valid, and runs successfully (when run directly). I still get the error if there's nothing dynamic in the SQL (it's just a single string with no concatenation).

CREATE TRIGGER TR_ContentItems ON ContentItems FOR INSERT     AS     BEGIN         PRINT @tableName     END 

I also get the same error whether using sysname or nvarchar(max) for the parameter.

 


If you execute your create trigger statement that you said you printed... you will find that it does not work. The print statement in the body of the trigger is trying to output @tablename, but is never defined, so you will get an error:

Must declare the scalar variable "@tableName".

But that is not your main issue. As for why you can't seem to execute a DDL statement with execute_sql with parameters, I couldn't find any documentation to explain why... but your experience and others proves that it's troublesome. I believe this post has a pretty good theory: sp_executesql adds statements to executed dynamic script?

You can however execute dynamic sql with DDL statements using the EXECUTE statement. So what you could do is create a parameterized sp_executesql statement that validates your table name and then creates a dynamic sql string to execute with the EXECUTE statement.

It doesn't look pretty, but it works:

DECLARE @tableName sysname = 'MyTable'; DECLARE @sql nvarchar(max) =  N' set @tableName = (SELECT name FROM sys.tables WHERE OBJECT_ID = OBJECT_ID(@tableName)) --validate table DECLARE @CreateTriggerSQL as varchar(max) = '' CREATE TRIGGER '' + QUOTENAME(''TR_'' + @tableName) + '' ON '' + QUOTENAME( @tableName) + '' FOR INSERT AS BEGIN     PRINT '''''' + @tableName + '''''' END '' print isnull(@CreateTriggerSQL, ''INVALID TABLE'') exec (@CreateTriggerSQL) ';  EXEC sp_executesql @sql, N'@tableName sysname', @tableName=@tableName; 

You could also convert this into a stored procedure with parameters instead of running sp_executesql if that were more convenient. It looks a bit cleaner:

CREATE PROCEDURE sp_AddTriggerToTable (@TableName AS sysname) AS  set @tableName = (SELECT name FROM sys.tables WHERE OBJECT_ID = OBJECT_ID(@tableName)) --validate table DECLARE @CreateTriggerSQL as varchar(max) = ' CREATE TRIGGER ' + QUOTENAME('TR_' + @tableName) + ' ON ' + QUOTENAME( @tableName) + ' FOR INSERT AS BEGIN     PRINT ''' + @tableName + ''' END ' print isnull(@CreateTriggerSQL, 'INVALID TABLE') exec (@CreateTriggerSQL) GO 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: