SQL - Populating multiple variables in the same query

Question

I am trying to load multiple variables in the same query like this

declare @currentUserPersonnelNumber int
declare @draftWorkFlowStatusId int 
declare @diWorkFlowStatusId int 
declare @ibWorkFlowStatusId int 
declare @ipWorkFlowStatusId int 

select 
    @draftWorkFlowStatusId = case when WFStep='DR' then WorkFlowId else NULL end,
    @diWorkFlowStatusId = case when WFStep='DI' then WorkFlowId else NULL end,
    @ibWorkFlowStatusId = case when WFStep='IB' then WorkFlowId else NULL end,
    @ipWorkFlowStatusId = case when WFStep='IP' then WorkFlowId else NULL end
 from WorkFlow

But only second variable @diWorkFlowStatusId is getting populated and not all.

What is wrong am I doing?

When I do it this way all the variable gets loaded properly, but I think that is not the right way

declare @draftWorkFlowStatusId int = (SELECT WorkFlowId FROM [WorkFlow] WHERE WFStep = 'DR')
declare @diWorkFlowStatusId int = (SELECT WorkFlowId FROM [WorkFlow] WHERE WFStep = 'DI') 
declare @ibWorkFlowStatusId int = (SELECT WorkFlowId FROM WorkFlow WHERE WFStep = 'IB') 
declare @ipWorkFlowStatusId int = (SELECT WorkFlowId FROM WorkFlow WHERE WFStep = 'IP')

I dont see any issue with your query case query ,except variable output will be overridden by last one. — TheGameiswar
– TheGameiswar, Commented Jun 15, 2016 at 9:44

sagi · Accepted Answer · 2016-06-15 09:47:11Z

3

You have to use an aggregation function :

declare @currentUserPersonnelNumber int
declare @draftWorkFlowStatusId int 
declare @diWorkFlowStatusId int 
declare @ibWorkFlowStatusId int 
declare @ipWorkFlowStatusId int 

select 
    @draftWorkFlowStatusId = MAX(case when WFStep='DR' then WorkFlowId end),
    @diWorkFlowStatusId = MAX(case when WFStep='DI' then WorkFlowId end),
    @ibWorkFlowStatusId = MAX(case when WFStep='IB' then WorkFlowId end),
    @ipWorkFlowStatusId = MAX(case when WFStep='IP' then WorkFlowId end)
 from WorkFlow

Your select as consturcted , can get only a single variable value at a time, since each of this variables are evaluated each time for each record, therefore - the MAX()

If there are more then 1 record that answer the critiria WFStep = ? , then you should tell us which one of them you want.

edited Jun 15, 2016 at 9:47

answered Jun 15, 2016 at 9:42

sagi

40.6k7 gold badges66 silver badges87 bronze badges

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

9 Comments

Pawan Nogariya Over a year ago

Excellent!! Thanks! The only thing is it is giving this warning in the message Warning: Null value is eliminated by an aggregate or other SET operation.

sagi Over a year ago

@PawanNogariya That's OK, I'm not sure why this warning is occuring but not problem with this query, also note that you don't have to use ELSE NULL since its the default ELSE .

Pawan Nogariya Over a year ago

Got it! One more thing, is this the efficient way to load variables or you suggest something else better?

sagi Over a year ago

I can't think of any better way of doing this @PawanNogariya

sagi Over a year ago

Yes, that's what he meant @thegameiswar

|

PersonThing · Accepted Answer · 2016-06-15 09:41:38Z

0

It's setting all your variables for every row retrieved from workflow. If only diWorkflowStatusId is set at the end, it's because your last row has WFStep = 'DI'.

answered Jun 15, 2016 at 9:41

PersonThing

4081 gold badge3 silver badges10 bronze badges

Comments

HoneyBadger · Accepted Answer · 2016-06-15 09:41:55Z

0

Your query returns multiple records, in which case the variables are populated with the values of the last record selected (since you don't give an order by clause it's unpredictable which record that is. Apparently, it's the record where WFStep='DI').

answered Jun 15, 2016 at 9:41

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

Collectives™ on Stack Overflow

SQL - Populating multiple variables in the same query

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related