Insert variables into a SQL Server table in stored procedure

Question

I am trying to insert variables into SQL Server table dynamically inside a stored procedure but I'm not able to accomplish it because of the syntax. Can someone correct me what am I doing wrong in the below code. Thanks.

Code I have tried:

SET @SQLStmt1 = N'INSERT INTO TestTable (TableName) 
                  VALUES(' + @ResulTableName + N')'
EXEC sp_executesql @SQLStmt1

score 2 · Accepted Answer · answered Jun 06 '17 at 01:00

2

You are close, but you should use a parameter for the value:

SET @SQLStmt1 = N'INSERT INTO TestTable( TableName ) 
                              VALUES(@ResulTableName)';

EXEC sp_executesql @SQLStmt1, N'@ResultTableName NVARCHAR(MAX)', 
                   @ResulTableName = @ResulTableName;

If you printed out @SQLStmt1, you would see that the single quotes are not correct.

answered Jun 06 '17 at 01:00

Gordon Linoff

1,242,037
58
646
786

How to do this if I have more than one column in the table? – Teja Jun 06 '17 at 01:11
2

You just add additional parameters for the columns. – Gordon Linoff Jun 06 '17 at 01:11

score -1 · Answer 2 · answered Jun 06 '17 at 01:09

-1

you can do it by adding triple quotes ''' this will resolve your problem

SET @SQLStmt1 = N'INSERT INTO TestTable( TableName ) 
                              VALUES(''' + @ResulTableName + ''')'
EXEC sp_executesql @SQLStmt1

the reason behind that is that triple quote in the dynamic SQL will become a single quote when the code get executed.

answered Jun 06 '17 at 01:09

asmgx

7,328
15
82
143

1

This is a bad practice, particularly unnecessary when using `sp_executesql` which accepts parameters. – Gordon Linoff Jun 06 '17 at 01:12

Insert variables into a SQL Server table in stored procedure

2 Answers2