Variables in a TSQL Stored Procedure
Posted on 2011-03-15
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?
WHEN CDR_Lookup_Values.Minimum is not null THEN
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))
WHEN CDR_Lookup_Values.Minimum is null THEN
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))
END AS Agent_New,
Agent_New * 10 AS Agent_NewX10,
Agent_NewX10 * 10 AS Agent_NewX100
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