CODEWITHSUNDEEP
sqlsql-servert-sql
Bud
Bud

Reputation: 781

Microsoft SQL Server - Conditional Variable Assignment

I'm trying to do something like this in Microsoft SQL Server:

DECLARE @isparent int;

SET @isparent = iif(account.parentid is not null,
                    1,
                    0);

SELECT
    IIF(@isparent = 1,
        parentenrollment,
        enrollment)            "Enrollment"
FROM
    Accounts

Obviously this is greatly simplified, but the idea is to store in a variable (@isparent, above) a value based on something from a table and then later re-read that variable, using it to determine what values to show.

Is this possible? If so, how?

Upvotes: 0

Views: 102

Answers (2)

Ilyes
Ilyes

Reputation: 14928

According to your code, you don't need to the variable, I think you are trying to do as

SELECT
    IIF(parentid is not null,
        parentenrollment,
        enrollment) Enrollment
FROM
    Accounts

and it's the same as

SELECT CASE WHEN ParentId IS NOT NULL THEN ParentEnrollment
            ELSE Enrollment
       END Enrollment
FROM Accounts

If you really want to check if parentid is null or not and store the value in a variable, then your query should return one (1) row, that can be done with two ways

  • TOP and ORDER BY

    SELECT TOP 1 @isparent = IIF(parentid IS NOT NULL, 1, 0)
FROM Accounts
ORDER BY <Your Order Here>

  • WHERE clause with conditions that returns only 1 row

    SELECT @isparent = IIF(parentid IS NOT NULL, 1, 0)
FROM Accounts
WHERE <Type Your Condition(s) Here>

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175934

You could use:

DECLARE @isparent int;

SELECT @isparent = IIF(parentid is not null, parentenrollment, enrollment)
FROM    Accounts
WHERE ... --  condition that returns single row

Upvotes: 0

Related Questions