SQL Server insert parameterized name table to IF EXISTS

Question

I want to insert @inputData to ColumnData if it doesn't exists to prevent duplicate data to a table with name as parameter @TableName. Error at the 'dbo.@TableName'.

SET @insertSQL = 'INSERT INTO '+ @TableName + ' (ColumnData) VALUES ('''+@inputData+''');'

IF NOT EXISTS (SELECT 1 FROM [dbo].[@TableName] WHERE ColumnData = @inputData)
    EXECUTE(@insertData) -- EXECUTE @insertData if ColumnData is not found

I also tried this (also throws error):

SET @insertSQL = 'INSERT INTO '+ @TableName + ' (ColumnData) VALUES ('''+@inputData+''');'

IF NOT EXISTS (EXECUTE('SELECT 1 FROM [dbo]. '+@TableName ' WHERE ColumnData = ' + @inputData))
    EXECUTE(@insertData)   -- EXECUTE @insertData if ColumnData is not found

What is the error you are getting? Can you share a screenshot? — Alok Gupta, Feb 21 '16 at 11:40
Invalid object name 'dbo.@Tablename' --The @Tablename didnt pass the parameter but instead use the '@Tablename' as its Table name — Brian Sanchez, Feb 21 '16 at 11:43

score 0 · Accepted Answer · edited May 23 '17 at 11:45

It look like you are splitting the dynamic SQL statement. Try something like this:

DECLARE @SQL NVARCHAR(MAX) = N'
IF NOT EXISTS (SELECT 1 FROM dbo.' + QUOTENAME(@TableName)  + ' WHERE ColumnData = @inputData
   INSERT INTO ' + QUOTENAME(@TableName) + ' (ColumnData) VALUES (@inputData)'

EXEC sp_executesql @SQL, N'@inputData VARCHAR(128)', @inputData = @inputData

Table name cannot be parameterized, so it must be appended. However, QUOTENAME is strongly recommended to avoid SQL injection.

On the other hand, @inputData can be sent as a parameter, that's why sp_executesql can be used.

Generally speaking, unless you are not dealing with parameters, try to always use sp_executesql instead of simple EXEC (), as EXEC forces into ugly string concatenation and possible injectable SQL statements.

SQL Server insert parameterized name table to IF EXISTS

1 Answers1