asked on

Variables in a TSQL Stored Procedure

I am creating a stored procedure and would like to know if there is a way to assign calculated values from the select statement to a variable so that they can be used elsewhere in the same select statement. Here's an example. I want to return the Agent_New column AND set it to a variable so that I can use it in another calcuation later on in the same statement creating a new name, Agent_NewX10, and then later on as Agent_NewX100.

Is this even possible?
Thanks!

SELECT *,
      CASE
      WHEN CDR_Lookup_Values.Minimum is not null THEN
            CASE
                  WHEN CDR_Raw_Data.Agent_Time <= CDR_Lookup_Values.Minimum THEN ceiling(CDR_Lookup_Values.Minimum)
                  WHEN CDR_Raw_Data.Agent_Time <= CDR_Lookup_Values.Threshold_A THEN ceiling(CDR_Raw_Data.Agent_Time + ((CDR_Raw_Data.Agent_Time * CDR_Lookup_Values.Increase_A)/100))
                  ELSE CDR_Raw_Data.Agent_Time
            END
      WHEN CDR_Lookup_Values.Minimum is null THEN
            CASE
                  WHEN CDR_Raw_Data.Agent_Time <= 12 THEN ceiling(CDR_Lookup_Values.Minimum)
                  WHEN CDR_Raw_Data.Agent_Time <= 30 THEN ceiling(CDR_Raw_Data.Agent_Time + ((CDR_Raw_Data.Agent_Time * 75)/100))
                  ELSE CDR_Raw_Data.Agent_Time
            END
      END AS Agent_New,
Agent_New * 10 AS Agent_NewX10,
Agent_NewX10 * 10 AS Agent_NewX100
FROM dbo.CDR_Raw_Data
left join dbo.CDR_Lookup_Values on CDR_Lookup_Values.Skill_ID = CDR_Raw_Data.skill_no
WHERE (CDR_Raw_Data.start_date >= @datefrom AND CDR_Raw_Data.start_date <= @dateto) AND CDR_Raw_Data.skill_no= @skill_no

knightEknight

Since you are in a stored procedure you can do it in two queries -- the first to set the variable and the second to return the dataset:

declare @AgentNew money -- or int, float, whatever is appropriate

SELECT @AgentNew =
CASE
WHEN CDR_Lookup_Values.Minimum is not null THEN
CASE
WHEN CDR_Raw_Data.Agent_Time <= CDR_Lookup_Values.Minimum THEN ceiling(CDR_Lookup_Values.Minimum)
WHEN CDR_Raw_Data.Agent_Time <= CDR_Lookup_Values.Threshold_A THEN ceiling(CDR_Raw_Data.Agent_Time + ((CDR_Raw_Data.Agent_Time * CDR_Lookup_Values.Increase_A)/100))
ELSE CDR_Raw_Data.Agent_Time
END
WHEN CDR_Lookup_Values.Minimum is null THEN
CASE
WHEN CDR_Raw_Data.Agent_Time <= 12 THEN ceiling(CDR_Lookup_Values.Minimum)
WHEN CDR_Raw_Data.Agent_Time <= 30 THEN ceiling(CDR_Raw_Data.Agent_Time + ((CDR_Raw_Data.Agent_Time * 75)/100))
ELSE CDR_Raw_Data.Agent_Time
END
END AS Agent_New,
FROM dbo.CDR_Raw_Data
left join dbo.CDR_Lookup_Values on CDR_Lookup_Values.Skill_ID = CDR_Raw_Data.skill_no
WHERE (CDR_Raw_Data.start_date >= @datefrom AND CDR_Raw_Data.start_date <= @dateto) AND CDR_Raw_Data.skill_no= @skill_no

select @AgentNew as AgentNew, @Agent_New * 10 AS Agent_NewX10, @Agent_New * 100 AS Agent_NewX100

Ephraim Wangoya

You cant do that.

You can either use a CTE or a temp table or you have to do the recalculation on every field you need to use the result in

ASKER CERTIFIED SOLUTION

knightEknight

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

knightEknight

from CTE

knightEknight

sorry - to clarify, the last statement should be:

select CTE.*, CTE.Agent_New * 10 AS Agent_NewX10, CTE.Agent_New * 100 AS Agent_NewX100
from CTE

HainKurt

you can wrap it with another select

select agent, agent*10 as agentx10
from (select 23 as agent) x

agent agentx10
23 230

HainKurt

in your case

select x.*,
Agent_New * 10 AS Agent_NewX10,
Agent_NewX10 * 10 AS Agent_NewX100
From (
SELECT *, CASE ... END AS Agent_New
FROM dbo.CDR_Raw_Data
left join dbo.CDR_Lookup_Values on CDR_Lookup_Values.Skill_ID = CDR_Raw_Data.skill_no
WHERE (CDR_Raw_Data.start_date >= @datefrom AND CDR_Raw_Data.start_date <= @dateto) AND CDR_Raw_Data.skill_no= @skill_no
) x

RichardRiga

ASKER

Thanks! This was exactly what I needed to see!