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

Gordon Linoff · Accepted Answer · 2017-06-06 01:00:10Z

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 6, 2017 at 1:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Teja Over a year ago

How to do this if I have more than one column in the table?

Gordon Linoff Over a year ago

You just add additional parameters for the columns.

asmgx · Accepted Answer · 2017-06-06 01:09:30Z

-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 6, 2017 at 1:09

asmgx

8,17020 gold badges102 silver badges186 bronze badges

1 Comment

Gordon Linoff Over a year ago

This is a bad practice, particularly unnecessary when using sp_executesql which accepts parameters.

Collectives™ on Stack Overflow

Insert variables into a SQL Server table in stored procedure

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related