T-SQL IF CLAUSE SYNTAX

Question

I'm trying to build up a sp for scheduling a mailing list depending on various parameters, and it is working fine up to this point:

DECLARE
@out_desc VARCHAR(1000),@out_mesg VARCHAR(10),@tableHTML  NVARCHAR(MAX),@frequency INT,@userName    NVARCHAR(50),@userEmail NVARCHAR(50),
@category NVARCHAR(50),@range INT,@geoid int,@IsDeleted BIT,@searchCity NVARCHAR(50);

DECLARE C1 CURSOR READ_ONLY
FOR
SELECT [userEmail], AspNetUsers.[UserName], [frequency], [category], [range], [geoid], [searchCity], [IsDeleted] FROM WishLists
JOIN AspNetUsers ON WishLists.userEmail = AspNetUsers.Email

OPEN C1
FETCH NEXT FROM C1 INTO
@userEmail, @userName,@frequency, @category,@range, @geoid, @searchCity, @IsDeleted
WHILE @@FETCH_STATUS = 0
BEGIN
      IF @frequency = 1
      BEGIN
            SET @tableHTML =  
    N'<H1>Hello' +  @userName +
    N'<table border="1"><tr><th>Department</th>th>Name</th><th>Description</th><th>Place</th></tr>' +  

        CAST (( SELECT td = c.Name, '',   td = p.Name, '',   td = p.Description, '', td = @searchCity
                  FROM Products as p JOIN Categories AS c ON c.ID = p.CategoryID  JOIN AspNetUsers as u ON u.ID = p.UserID WHERE (p.IsApproved = 'true' AND p.IsDeleted = 'false'  AND u.Email = @userEmail) AND DateExpire > convert(date, getdate())
                  FOR XML PATH('tr'), TYPE   
        ) AS NVARCHAR(MAX) ) 
    +  
    N'</table>' ; 

    EXEC sp_send_mail
    ...

This works as expected. Now that I have tested the emailing function I need to further elaborate the SELECT with another "IF" clause where a specific geolocation has been selected.

I'm trying to insert this new IF clause adding another SELECT this way:

    ...
N'<th>Place</th></tr>' +  

        CAST (
IF @geoid = 0 (
    (SELECT td = c.Name, '',  
                        td = p.Name, '',  td = p.Description, '', td = @searchCity
                  FROM Products as p 
                  ...)
                  FOR XML PATH('tr'), TYPE   
        ) AS NVARCHAR(MAX) 
)
ELSE
    (...geoid > 0 => another select here...)
    ) 
    +  
    N'</table>' ; 

but just inserting the IF @geoid = 0 () block it looks like there's something wrong with the syntax. What am I doing wrong?

Answer 1

use the case statement instead of IF as below

N'<th>Place</th></tr>' +  

        CAST (
CASE WHEN @geoid = 0 THEN (
    (SELECT td = c.Name, '',  
                        td = p.Name, '',  td = p.Description, '', td = @searchCity
                  FROM Products as p 
                  ...)
                  FOR XML PATH('tr'), TYPE   
        ) AS NVARCHAR(MAX) 
)
ELSE
    (...geoid > 0 => another select here...)
    ) 
    +  
    N'</table>' ; 

because the If statement can not be used inside other operations like SELECT or SET

Something Like this

DECLARE @V_String VARCHAR(MAX)
SELECT 
    @V_String = CASE WHEN SalesOrderID%2 = 1
                    THEN (SELECT DueDate FROM SalesLT.SalesOrderHeader FOR XML PATH(''))
                ELSE (SELECT ShipDate FROM SalesLT.SalesOrderHeader FOR XML PATH('')
                ) 
                END
    FROM SalesLT.SalesOrderHeader S

SELECT
    @V_String