CODEWITHSUNDEEP
sqlsql-servert-sql
Prof. Falken
Prof. Falken

Reputation: 549

Set a Variable Using IF/ELSE That Contains SELECT Statement

I am running in syntax errors when trying to set a variable to a string. The string is constructed in an IF/ELSE statement that will provide a variation of the message based on a field being null. I am getting the syntax error on the SELECT after the set @errorMsg line. The variable will then be called in another query. At this point I am not sure if it is a problem with the query or if I am looking at this wrong.

declare @errorMsg nvarchar(MAX)
declare @combinedString VARCHAR(MAX)

IF EXISTS (select y from db.b)
    BEGIN
    set @errorMsg =
        SELECT @combinedString = COALESCE(@combinedString + ', ', '') + x_y
        from (select CONCAT(cg.x, '-', f.y) AS x_y
        from db.a cg
        LEFT JOIN db.b f
        ON cg.x = f.y) AS w
        SELECT 'The x listed in the database are (x - y if applicable): ' + @combinedString as StringValue
    END
ELSE
    BEGIN
    set @errorMsg =
        SELECT @combinedString = COALESCE(@combinedString + ', ', '') + x_y
        from (select cg.x AS x_y
        from db.a) AS w
        SELECT 'The x listed in the database are (x - y if applicable): ' + @combinedString as StringValue
    END

Upvotes: 0

Views: 1010

Answers (1)

user1178830
user1178830

Reputation: 446

Looking at your query, the 'SET @errorMsg' might just be in the wrong place.

declare @errorMsg nvarchar(MAX)
declare @combinedString VARCHAR(MAX)

IF EXISTS (select y from db.b)
    BEGIN
        SELECT @combinedString = COALESCE(@combinedString + ', ', '') + x_y
        from (select CONCAT(cg.x, '-', f.y) AS x_y
        from db.a cg
        LEFT JOIN db.b f
        ON cg.x = f.y) AS w;
        SET @errorMsg ='The x listed in the database are (x - y if applicable): ' + @combinedString
    END
ELSE
    BEGIN
        SELECT @combinedString = COALESCE(@combinedString + ', ', '') + x_y
        from (select cg.x AS x_y
        from db.a) AS w;
        SET @errorMsg = 'The x listed in the database are (x - y if applicable): ' + @combinedString
    END

Upvotes: 1

Related Questions