?
Solved

Variables in a TSQL Stored Procedure

Posted on 2011-03-15
8
Medium Priority
?
327 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: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 Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 61

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 61

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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

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

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…
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

589 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