Pawan Nogariya Pawan Nogariya - 5 months ago 7
SQL Question

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

Answer

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.