Incorrect syntax near the keyword CASE when trying to use in storedprocedure-CodePudding

Check the SQL Query below in storedprocedure. I am trying to implement CASE to set some values to the @Query variable, but the CASE syntax is throwing error:

Incorrect syntax near the keyword 'CASE'.

DECLARE @Query AS VARCHAR(max);

CASE
    WHEN @Type=1 THEN 
    SET @Query = ''
    WHEN @Type=2 THEN
    SET @Query = ''
    WHEN @Type=3 THEN 
    SET @Query = ''
    ELSE
    SET @Query = '';
    
END;

CodePudding user response：

You want a CASE expression used in a single SET:

SET @query = (CASE WHEN @Type = 1 THEN ''
                   WHEN @Type = 2 THEN ''
                   WHEN @Type = 3 THEN ''
                   ELSE ''
              END);

Of course, everything in your CASE is setting the value to '', so conditional logic is not needed. I assume your actual code has different values.

CodePudding user response：

You should start with SELECT, otherwise it's not a valid CASE statement. Also maybe you need to store the Result of the CASE in a separate Variable like SELECT @Query = CASE WHEN ...