SQL errors with dynamic query

Question

This is probably very simple to fix, but I just can't seem to see the problem.

Here are the errors:

Msg 102, Level 15, State 1, Line 30
Incorrect syntax near 'TTP ID'.

Msg 137, Level 15, State 2, Line 36
Must declare the scalar variable "@TransPeriodID".

Here is the script:

create procedure [dbo].[InsertPayrollTransactions] 
    @TransPeriodID int, 
    @PayrollID int, 
    @TableCode int
as
begin
    DECLARE @SQLString NVARCHAR(MAX)
    DECLARE @ParmDefinition NVARCHAR(500)

    IF @TableCode > 5 OR @TableCode < 0
    BEGIN
        SET @TableCode = 0
    END

    SELECT @SQLString = 
       'INSERT INTO [dbo].[TA Payroll Transactions' + CASE @TableCode
                                                                                                   WHEN 0 THEN ''
                   WHEN 1 THEN '1'
                   WHEN 2 THEN '2'
                   WHEN 3 THEN '3'
                   WHEN 4 THEN '4'
                   WHEN 5 THEN '5'
        END + '] ([TTP ID], [Payroll ID])
        VALUES (@TransPeriodID, @PayrollID)'

    SET @ParmDefinition = N'([TTP ID] int, [Payroll ID] int)'

    -- PRINT @SQLString
    EXECUTE sp_executesql @SQLString, 
               @ParmDefinition, 
               @TransPeriodID = @TransPeriodID,
               @PayrollID = @PayrollID
end

I had a different way of doing this (not using dynamic SQL), but a co-worker suggested this. He gave me an example for a similar script, and that works fine, but obviously I got something wrong when applying it to this script. I've tried to compare where I might have missed something (comma or apostrophe) but to no avail.

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

Before using Dynamic SQL read sp_executesql. Wrongly used can cause more problems than benefits and allow SQL-Injection attacks.

sp_executesql [ @stmt = ] statement [ { , [ @params = ]

N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }

{ , [ @param1 = ] 'value1' [ ,...n ] } ]

You need to set params:

/* Yours */
SET @ParmDefinition = N'([TTP ID] int
    , [Payroll ID] int)'

/* Correct */
SET @ParmDefinition = N'@TransPeriodID INT
    , @PayrollID INT';

EXECUTE [dbo].[sp_executesql]
        @SQLString
       ,@ParmDefinition
       ,@TransPeriodID
       ,@PayrollID;

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 28, 2015 at 12:24

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

1 Comment

Igavshne Over a year ago

Thanks for the link as well.

Collectives™ on Stack Overflow

SQL errors with dynamic query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related