Microsoft SQL Server - Conditional Variable Assignment

Question

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?

I suppose that account.parentid is a typo and you mean accounts.parentid — Ilyes
– Ilyes, Commented May 4, 2019 at 21:45

Ilyes · Accepted Answer · 2019-05-04 21:37:07Z

1

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>

edited May 4, 2019 at 21:37

answered May 4, 2019 at 21:25

Ilyes

15k5 gold badges31 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukasz Szozda · Accepted Answer · 2019-05-04 21:24:20Z

0

You could use:

DECLARE @isparent int;

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

answered May 4, 2019 at 21:24

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

Microsoft SQL Server - Conditional Variable Assignment

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related