CODEWITHSUNDEEP
sqlsql-server
Pawan Nogariya
Pawan Nogariya

Reputation: 8970

SQL - Populating multiple variables in the same query

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')

Upvotes: 2

Views: 266

Answers (3)

sagi
sagi

Reputation: 40481

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.

Upvotes: 3

HoneyBadger
HoneyBadger

Reputation: 15150

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').

Upvotes: 0

PersonThing
PersonThing

Reputation: 388

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'.

Upvotes: 0

Related Questions