Solved

Variables in a TSQL Stored Procedure

Posted on 2011-03-15
8
317 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:RichardRiga
8 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35138547
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
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35138576
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
0
 
LVL 33

Accepted Solution

by:
knightEknight earned 500 total points
ID: 35138588
I just saw that you are also selecting * from the tables, so you would indeed have to re-query the tables using the method above (which I presume is what  you are trying to avoid).  Let's try something different:


;  -- this is necessary for this syntax

with CTE as (
  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,
  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 CTE.*, CTE.Agent_New * 10 AS Agent_NewX10, CTE.Agent_New * 100 AS Agent_NewX100

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 33

Expert Comment

by:knightEknight
ID: 35138600
from CTE
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35138614
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
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 35138997
you can wrap it with another select

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

agent      agentx10
23      230
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 35139021
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
0
 
LVL 1

Author Closing Comment

by:RichardRiga
ID: 35147367
Thanks! This was exactly what I needed to see!
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question