T-SQL, assign values to multiple variable from the same column in one SELECT

Question

I'd like to manipulate variables into behaving like enums,

CREATE TABLE dbo.ActionType
(
    ActionID INT,
    Name NVARCHAR(30)
)

INSERT INTO dbo.ActionType 
    (ActionID, Name)
VALUES 
    (100, N'Product_Insert'), 
    (101, N'Product_Update'), 
    (102, N'Product_Delete')

DECLARE @ACTION_INSERT TINYINT,
        @ACTION_UPDATE TINYINT,
        @ACTION_DELETE TINYINT

SELECT  @ACTION_INSERT = ActionID, 
        @ACTION_UPDATE = ??, -- how would I do this properly?
        @ACTION_DELETE = ??  -- or is it possible?
FROM dbo.ActionType
WHERE Name LIKE 'Product_%'

-- Test result, which always returns null
SELECT @ACTION_INSERT, @ACTION_UPDATE, @ACTION_DELETE

I know I could use CURSOR, but is there a better approach to doing this without iteration?

Possible duplicate of stackoverflow.com/questions/469318/… — Matthew Verstraete
– Matthew Verstraete, Commented Dec 1, 2015 at 17:22
@MatthewVerstraete The accepted answer there was for multiple table columns; while in my case, I need these three variables be assigned with different values of different rows of one column. — Yom T.
– Yom T., Commented Dec 1, 2015 at 17:27

Anup Agrawal · Accepted Answer · 2015-12-01 17:36:39Z

2

You need to pivot your data to get the result you want.

SELECT  @ACTION_INSERT = MAX(CASE WHEN Name = 'Product_Insert' THEN ActionID END), 
        @ACTION_UPDATE = MAX(CASE WHEN Name = 'Product_Update' THEN ActionID END),
        @ACTION_DELETE = MAX(CASE WHEN Name = 'Product_Delete' THEN ActionID END)
FROM dbo.ActionType
WHERE Name LIKE 'Product_%'

Complete Code:

CREATE TABLE dbo.ActionType
(
    ActionID INT,
    Name NVARCHAR(30)
)

INSERT INTO dbo.ActionType 
    (ActionID, Name)
VALUES 
    (100, N'Product_Insert')
INSERT INTO dbo.ActionType 
    (ActionID, Name)
VALUES      
    (101, N'Product_Update') 
INSERT INTO dbo.ActionType 
    (ActionID, Name)
VALUES 
    (102, N'Product_Delete')

DECLARE @ACTION_INSERT INT,
        @ACTION_UPDATE INT,
        @ACTION_DELETE INT

SELECT  @ACTION_INSERT = MAX(CASE WHEN Name = 'Product_Insert' THEN ActionID END), 
        @ACTION_UPDATE = MAX(CASE WHEN Name = 'Product_Update' THEN ActionID END),
        @ACTION_DELETE = MAX(CASE WHEN Name = 'Product_Delete' THEN ActionID END)
FROM dbo.ActionType
WHERE Name LIKE 'Product_%'

SELECT @ACTION_INSERT, @ACTION_UPDATE, @ACTION_DELETE

answered Dec 1, 2015 at 17:36

Anup Agrawal

6,6891 gold badge30 silver badges33 bronze badges

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

1 Comment

Yom T. Over a year ago

OK. Even though that's a bit longer but it nailed it. I would definitely pick this solution rather than having to hardcode the real values. Thanks!

Collectives™ on Stack Overflow

T-SQL, assign values to multiple variable from the same column in one SELECT

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related