SQL Server 2012 – Troubleshooting sp_executesql Parameter String

sql-server-2012t-sql

I've stared myself blind at this. It started as a more complex procedure, but I've stripped it down to the bare bones, trying to make it run. This is the current code:

DECLARE @sql NVarchar(MAX) = '
CREATE PROCEDURE dbo.Test
AS
BEGIN
    SELECT 1, @num;
END'

EXEC [sp_executesql] @sql, N'@num int', @num = 1;

It simply refuses. It's worth mentioning that if I run the code without the parameter-part (@sql being the sole parameter to the sp_executesql function) it runs fine. I've tried other syntax alternatives, such as 

EXEC [sp_executesql] @sql, N'@num int', 1;

and 

DECLARE @params NVarchar(1000) = '@num int';
EXEC [sp_executesql] @sql, @params, 1;

What is wrong with my parameter-specification?

Edit 1: Included error-message

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

Best Answer

You seem to be expecting that the parameters will end up being treated as string interpolation arguments.

They won't.

One way of achieving something similar to what you want would be simply to parameterise the stored procedure

CREATE PROCEDURE dbo.Test
@num int
AS
BEGIN
    SELECT 1, @num;
END

Then when you execute it you supply the desired value.

If you definitely want the procedure to end up with @num replaced with a hardcoded 1 you can use.

DECLARE @num int = 1

DECLARE @sql NVarchar(MAX) = '
CREATE PROCEDURE dbo.Test
AS
BEGIN
    SELECT 1, @num;
END'

SET @sql = REPLACE(@sql, '@num', @num)

EXEC sys.sp_executesql @sql -- or just EXEC(@sql)

Take care of SQL injection if you go this route.

Also you would need to be careful of parameter/place holder names and order of replacements, so if you had place holders @num and @number the @number one would need to be replaced first.

Related Question