CODEWITHSUNDEEP
sqlsql-server
pythondumb
pythondumb

Reputation: 1207

SQL : Getting Incorrect Syntax near '=' While using CASE statement with variables

I am trying to perform the below query where some variables are being used. This below SQL code is a part of a stored procedure. Idea is to dynamically set the target columns and its values based on FileKey.

DECLARE @TargetColNames nvarchar(max) = '';
DECLARE @SourceColNames nvarchar(max) = '';
DECLARE @SourceColNamesInsert nvarchar(max) = '';
DECLARE @UpdateColumns nvarchar(max) = '';

SELECT 
    CASE 
        WHEN @FileKey IN ('s_1','s_2') 
            THEN @TargetColNames = @TargetColNames + ' [CreatedUser], [UpdatedUser], [CreatedDateTime],[UpdatedDateTime],[IsDeleted],[DeletedOn]'     
            ELSE @TargetColNames = @TargetColNames + ' [CreatedUser], [UpdatedUser], [CreatedDateTime], [UpdatedDateTime]' 
    END,
    @SourceColNames = CONCAT('CreatedUser','UpdatedUser','CreatedDateTime', 'UpdatedDateTime'),
    @SourceColNamesInsert = CONCAT(''',@User, ''',''',@User, ''', 'Getdate()', 'Getdate()' ),
    CASE 
        WHEN @FileKey IN ('s_1','s_2') 
            THEN @UpdateColumns = CONCAT('Target.UpdatedUser= ''',@User,''', 'Target.[IsDeleted]=0','Target.[DeletedOn]=null')
            ELSE @UpdateColumns = CONCAT('Target.UpdatedUser= ''',@User,''', 'Target.UpdatedDateTime=Getdate()')
    END

The above SQL statement throws an error:

Msg 102, Level 15, State 1, Procedure uspDynamicStageToPropLayer1, Line 165 [Batch Start Line 5]
Incorrect syntax near '='.

What am I missing here? Maybe this is quite a silly mistake...

Upvotes: 0

Views: 242

Answers (2)

DRapp
DRapp

Reputation: 48139

Also, by doing concat with your quoted parts, you might allow SQL injection into your query even building dynamically. What if a user's name (or forced parameter has a leading single quote, then garbage injection such as

@User = ';drop table X --

Having said that, some of the stuff could be more simplified, such as

SELECT
   @TargetColNames = @TargetColNames
       + ' [CreatedUser], [UpdatedUser], [CreatedDateTime], [UpdatedDateTime]'
       + CASE WHEN @FileKey IN ('s_1','s_2')
              THEN ', [IsDeleted], [DeletedOn]'
              else ''
         end

For the insert, you will probably get a failure. If you look at a possible result of the @User

@SourceColNamesInsert = concat(''',@User, ''',''',@User, ''',
'Getdate()', 'Getdate()' ),

will result with the value below which is not what I think is intended. Notice no comma's between values because the triple ' is creating start and end literals, and leaves no actual comma between column insert values.

',@User, '',@User, 'Getdate()Getdate()

But instead...

select concat('''@User'', ''@User''', ', Getdate(), Getdate()' );

which will result in...

'@User', '@User', Getdate(), Getdate()

The ''' actually creates an opening quoted string immediately with the value after it, then the '' (double) closes the quoted string, but it also adds the comma separator before the next '' (double) to start second user and ''' (triple) to close the second @User, then adding comma and both getdate() calls.

'@User', '@User', Getdate(), Getdate()

Now, if the value for @User was 'Bob', and your intent was to have the string output as

'Bob', 'Bob', Getdate(), Getdate()

change to select concat('''', @User, ''', ','''', @User, '''', ', Getdate(), Getdate()' );

The '''' (quad) means I want to open a string, do a single quote (by the inner two ''), and close this as its own string. then get the value of the @User. Then follow by an open string ' with '' for closing the quote around the name, then open a single quote to start the next, the comma before starting the next quote for the second user and closing it as well via '''', then the value of the user again, and finally closing the second user with close quote ''''. Finally adding a comma and getdate() calls. Yes, stupid tricky in the quoting.

An easier implementation without CONCAT() is just using + between each explicit part such as

select '''' + @User + '''' + ', ' + '''' + @User + '''' + ', Getdate(), getdate()' ;

where each '''' is a single quote thus resulting in

' + Bob + ' + , + ' + Bob + ' + , Getdate(), getdate()

resulting in

'Bob', 'Bob', Getdate(), getdate()

I'll leave the final UpdateColumns to you to confirm your intended output.

But, as mentioned, beware of possible SQL injection when you are dynamically building SQL statements with embedded parameter values as this appears to be doing.

Upvotes: 3

Jeff
Jeff

Reputation: 537

Here is another way to approach this:

Declare @FileKey                varchar(10) = 's_3'
      , @TargetColNames         nvarchar(max) = ''
      , @SourceColNames         nvarchar(max) = ''
      , @SourceColNamesInsert   nvarchar(max) = ''
      , @UpdateColumns          nvarchar(max) = '';

    Set @TargetColNames = concat_ws(', ', '[CreatedUser]', '[UpdatedUser]', '[CreatedDateTime]', '[UpdatedDateTime]');

     If @FileKey In ('s_1', 's_2')
        Set @TargetColNames = concat_ws(', ', @TargetColNames, '[UpdatedDateTime]', '[IsDeleted]', '[DeletedOn]');

  Print @TargetColNames;

    Set @SourceColNames = concat_ws(', ', '[CreatedUser]', '[UpdatedUser]', '[CreatedDateTime]', '[UpdatedDateTime]');
    Set @SourceColNamesInsert = concat_ws(', ', quotename('@User', char(39)), quotename('Getdate()', char(39)), quotename('Getdate()', char(39)));

  Print @SourceColNames;
  Print @SourceColNamesInsert;

Upvotes: 0

Related Questions