?
Solved

Variables in a TSQL Stored Procedure

Posted on 2011-03-15
8
Medium Priority
?
320 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 2000 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 57

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 57

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

741 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