CODEWITHSUNDEEP
sqlsql-serverif-statement
Jack Cole
Jack Cole

Reputation: 1804

Incorrect syntax near IF statement

I am using an IF statement in order to choose a column that isn't NULL in an SQL SELECT procedure. However, I get an error saying there is something wrong with my syntax near keyword IF.

DECLARE @imgURL_prefix VARCHAR(100)
DECLARE @imgSmall_Suffix VARCHAR(100)
DECLARE @imgLarge_Suffix VARCHAR(100)

SET @imgURL_prefix = '//sohimages.com/images/images_soh/'
SET @imgSmall_Suffix = '-1.jpg'
SET @imgLarge_Suffix = '-2.jpg'

SELECT
    P.ProductCode as ProductCode,
    P.HideProduct as HideProduct,
    P.Photos_Cloned_From as Photos_Cloned_From,
    @imgURL_prefix +
        LOWER( IF P.Photos_Cloned_From IS NOT NULL
            P.Photos_Cloned_From
        ELSE
            P.ProductCode
        END )
        + @imgSmall_Suffix as PhotoURL_Small,
    @imgURL_prefix +
        LOWER( IF P.Photos_Cloned_From IS NOT NULL
            P.Photos_Cloned_From
        ELSE
            P.ProductCode
        END )
        + @imgLarge_Suffix as PhotoURL_Large,
    P.PhotoURL_Small as OriginalSmall,
    P.PhotoURL_Large as OriginalLarge
FROM
    Products_Joined P

The script works fine without the IF statement, using just LOWER(P.ProductCode) in it's place.

Upvotes: 2

Views: 1213

Answers (1)

M.Ali
M.Ali

Reputation: 69514

You can use IIF(Expression, true, false) in sql server 2012 and later editions and for older editions you have CASE statement 

/*********  SQL SERVER 2012+ ***********/

SELECT
    P.ProductCode as ProductCode,
    P.HideProduct as HideProduct,
    P.Photos_Cloned_From as Photos_Cloned_From,
    @imgURL_prefix +
        LOWER( IIF( P.Photos_Cloned_From IS NOT NULL , P.Photos_Cloned_From, P.ProductCode))
        + @imgSmall_Suffix as PhotoURL_Small,
    @imgURL_prefix +
        LOWER( IIF (P.Photos_Cloned_From IS NOT NULL, P.Photos_Cloned_From, P.ProductCode))
        + @imgLarge_Suffix as PhotoURL_Large,
    P.PhotoURL_Small as OriginalSmall,
    P.PhotoURL_Large as OriginalLarge
FROM
    Products_Joined P

/*********  SQL SERVER Older Versions ***********/

SELECT
    P.ProductCode as ProductCode,
    P.HideProduct as HideProduct,
    P.Photos_Cloned_From as Photos_Cloned_From,
    @imgURL_prefix +
        LOWER( CASE WHEN P.Photos_Cloned_From IS NOT NULL 
              THEN P.Photos_Cloned_From ELSE P.ProductCode END)
        + @imgSmall_Suffix as PhotoURL_Small,
    @imgURL_prefix +
        LOWER( CASE WHEN P.Photos_Cloned_From IS NOT NULL 
               THEN P.Photos_Cloned_From ELSE P.ProductCode END)
        + @imgLarge_Suffix as PhotoURL_Large,
    P.PhotoURL_Small as OriginalSmall,
    P.PhotoURL_Large as OriginalLarge
FROM
    Products_Joined P

Upvotes: 2

Related Questions