SQL Server Incorrect syntax near

Question

I have the following T-SQL code that run in SQL Server Management Studio.

DECLARE JF_PF CURSOR FOR

SELECT a.Region, ..... (shorten for display) ;  

OPEN JF_PF;

FETCH NEXT FROM JF_PF 
INTO @varRegion, @varLocationId, @varTransDate, @varcontractTypeName, @varOriginalPrepaidDues, @varNewPrepaidDues, @varJoiningFee, @varPrepaidFeePackage;

WHILE @@FETCH_STATUS = 0  
BEGIN  
    IF @varcontractTypeName = 'New Join' and @varNewPrepaidDues = 'Dues'
          @varNewJoin_Dues_JF = @varJoiningFee;
    IF @varcontractTypeName = 'New Join' and @varNewPrepaidDues = 'Prepaid'
          BEGIN
                @varNewJoin_Prepaid_JF = @varJoiningFee;
                @varNewJoin_Prepaid_PF = @varPrepaidFeePackage;
          END;
    IF @varcontractTypeName = 'Renew' and @varOriginalPrepaidDues = 'Prepaid' and @varNewPrepaidDues = 'Prepaid'
          BEGIN
                @varRenew_PrepaidPrepaid_JF = @varJoiningFee;
                @varRenew_PrepaidPrepaid_PF = @varPrepaidFeePackage;
          END;
    IF @varcontractTypeName = 'Upgrade' and @varOriginalPrepaidDues = 'Dues' and @varNewPrepaidDues = 'Prepaid'
          BEGIN
                @varUpgrade_DuesPrepaid_JF = @varJoiningFee;
                @varUpgrade_DuesPrepaid_PF = @varPrepaidFeePackage;
          END;
    IF @varcontractTypeName = 'Upgrade' and @varOriginalPrepaidDues = 'Prepaid' and @varNewPrepaidDues = 'Prepaid'
          @varUpgrade_PrepaidPrepaid_PF = @varPrepaidFeePackage;

    FETCH NEXT FROM JF_PF 
    INTO @varRegion, @varLocationId, @varTransDate, @varcontractTypeName, @varOriginalPrepaidDues, @varNewPrepaidDues, @varJoiningFee, @varPrepaidFeePackage;
END;

CLOSE JF_PF ;  
DEALLOCATE JF_PF ;

When it is executed, I got the following message inside the cursor while loop.

Msg 102, Level 15, State 1, Line 105
Incorrect syntax near '@varNewJoin_Dues_JF'.

Msg 102, Level 15, State 1, Line 108
Incorrect syntax near '@varNewJoin_Prepaid_JF'.

Msg 102, Level 15, State 1, Line 113
Incorrect syntax near '@varRenew_PrepaidPrepaid_JF'.

Msg 102, Level 15, State 1, Line 118
Incorrect syntax near '@varUpgrade_DuesPrepaid_JF'.

Msg 102, Level 15, State 1, Line 122
Incorrect syntax near '@varUpgrade_PrepaidPrepaid_PF'.

Msg 102, Level 15, State 1, Line 126
Incorrect syntax near ';'.

Can someone please tell me what is wrong for the syntax? Thanks.

Jimmy

You are missing the keyword SET. Put it before the assignments (e.g. before @varNewJoin_Dues_JF = @varJoiningFee;). — FDavidov

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-03-26T05:54:29

In SQL Server, you need to use set or select to set variables. So:

set @varNewJoin_Prepaid_JF = @varJoiningFee;
set @varNewJoin_Prepaid_PF = @varPrepaidFeePackage;

Or, combine them into one statement using select:

select @varNewJoin_Prepaid_JF = @varJoiningFee,
       @varNewJoin_Prepaid_PF = @varPrepaidFeePackage;

Your particular code isn't doing anything other than setting variables. I wonder if you could eliminate the cursor from your code entirely.

SQL Server Incorrect syntax near

1 Answers