SQL Dynamic Stored Procedure

Question

I hope some one can put me out of my misery with what should be a simple issue but I am unable to see what I have done wrong. I am relatively new to SQL but this should be a straight forward conversion.

I have a simple SQL stored procedure as below

INSERT INTO Country (Country)
VALUES (@NewResourceName);

I wish to convert this into a dynamic stored procedure and have tried numerous variations of the below

DECLARE @sSQL nvarchar(400);
SET @sSQL = 'INSERT INTO '+@Resource+'('+@Resource+')'+
' VALUES ('+@NewResourceName+');'
Execute sp_executesql @sSQL;

I receive an error invalid column name for what ever I pass as parameter @NewResourceName

Thank you in advance.

Answer 1

Try this:

set @sSQL = 'INSERT INTO ' + @Resource + ' ('+ @Resource + ')' + ' VALUES (@NewResourceName)'

exec sp_executesql @sSQL, N'@NewResourceName nvarchar(max)', @NewResourceName

Answer 2

Hi @BigH61

I created an empty table test.

You can try this dynamic SQL.

DECLARE @sSQL nvarchar(400)
DECLARE @TableName nvarchar(max)
DECLARE @ColName nvarchar(max)
DECLARE @Insert nvarchar(max)
set @TableName = 'test'
set @ColName = 'A'
set @Insert = 'j24h'
set @sSQL = N'INSERT INTO ' + @TableName + '(' + @ColName + ')' + ' VALUES (' +  QUOTENAME(@Insert,'''') + ');'
print @sSQL;
Exec sp_executesql @sSQL,N'@TableName nvarchar(max),@ColName nvarchar(max),@Insert nvarchar(max)',@TableName,@ColName,@Insert;

Output:

If you need to write into stored procedures, you can refer to this official document, which contains an example of dynamic insertion.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver16

Best regards,

Percy Tang