Solved

Variables in a TSQL Stored Procedure

Posted on 2011-03-15
8
319 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:Ephraim Wangoya
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 54

Expert Comment

by:Huseyin KAHRAMAN
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 54

Expert Comment

by:Huseyin KAHRAMAN
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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

729 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