sp_executesql - What is wrong with my Parameter-string?

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'.

Answers 2

  • 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.


  • You should avoid making the entire call dynamic because of the sql injection vulnerability.

    Your procedure needs to accept the parameter you are specifying on your dynamic sql call as well

        @sql='
        CREATE PROCEDURE dbo.Test
        @num int
        AS
        BEGIN
            SELECT 1, @num;
        END'
        exec sp_executesql @sql
    

    The proper way to parameterize your dynamic sql is:

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

    Parameterizing dynamic sql also allows the plan in the procedure cache to be reused and is much more efficient for the server. If you ever come across dynamic sql that is not parameterized and you can't modify because it is in a third party software, you can also use plan guides.


Related Questions