Variables within as MS SQL query?

Posted on 2007-07-20
Last Modified: 2012-05-05
Is it pobbile to us a variable in a MS SQL query like this:


SELECT tre1.*,
(SELECT @ChrgTot = ISNULL(SELECT SUM Amt FROM Charges WHERE tre1.TreatId = Charges.TretId), 0) As Total,
50000 - @ChrgTot As Balance
FROM Treatments tre1

The reason for this request is that the subquery is quite lengthy and I don't want to have to repeate it several times within the query.  If I could execute the sub query once, then use it throughout the rest of the query, that would be great.
Any ideas would be much appreciated.
Question by:csalmond
    LVL 15

    Expert Comment

    No, i don't believe so.

    But, you can put the subquery into a function and then simply do

    select tre1.*, myfunction(x) .....
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    you can't
    LVL 29

    Expert Comment

    by:Alan Huseyin Kayahan
    Hi lontours,

               *Please open Symantec System Center Console.
               *Unlock your server group
               *Right-click server in left pane>All tasks>Symantec antivirus>Click server tuning options
              *Check the "Allow this server to manage 9.x and earlier versions..." box
              *Symantec antivirus service may require restart after that change


    LVL 29

    Expert Comment

    by:Alan Huseyin Kayahan
    sorry wrong question
    LVL 39

    Expert Comment

    i think you can make use of CTE

    try like this

    with mainQry as (SELECT tre1.*,
    ISNULL((SELECT SUM( Amt) FROM Charges WHERE tre1.TreatId = Charges.TretId), 0) As Total
    FROM Treatments tre1)
    Select *, 50000 - Total As Balance
     from mainQry
    LVL 42

    Expert Comment

    Yes, a CTE probably works. However, as suggested by appari, it does not solve your problem of needing to repeat the subquery again and again.  To use the subquery multiple times, you need to do it a little different.

    WITH myCTE
    --subquery goes here
    --reference the subquery here
    Select *, (select 50000 - Total from MYCTE), (select 6000-Total from MYCTE)
    FROM ....
    --or join to the subquery in the from clause

    In your case, something like:
    WITH ChargeCTE
    --subquery goes here
    (Select Charges.TretID, ISNULL(sum(Amt),0) As Total FROM Charges group by Charges.TretID)
    -- main query goes here
    Select Tre1, 50000-ChargeCTE.Total,  60000-ChargeCTE.Total
    FROM Treatments Tre1 inner join ChargeCTE on Treatments.TretID = ChargeCTE.TretID
    LVL 15

    Accepted Solution

    create function dbo.GetTotal(@id int)
    returns money
    SELECT SUM(Amt) FROM Charges WHERE TretId = @id

    SELECT tre1.*, dbo.GetTotal(tre1.TreatID) AS Total, 50000 - dbo.GetTotal(tre1.TreatID) AS Balance
    FROM Treatments tre1

    LVL 15

    Expert Comment

    You should also be able to do the following:

    SELECT tre1.*,  c.hgsTotal, 50000 - chgs.Total AS Balance
    FROM Treatments tre1
    INNER JOIN (SELECT TreatID, ISNULL(SUM(Amt), 0) FROM Charges GROUP BY TreatID) AS chgs ON tre1.TreatID = chgs.TreatID

    Author Comment

    Thanks everyone for contributing...
    I'm leaning toward the user defined function as
    1. I'm using sql 2000
    2. The query is very complex and I'm not sure how the inner join might affect the total result set

    However, the bummer about the user defined function is that this thing is stored in the database.  Is there are way to create a transient function, one that is only kept during the execution of the query?  The reason here is maintenance.  I'll need about 5 user defined functions for this 1 query, and they will not be needed anywhere else in the app.  Too bad the simple variable thing is not an option.

    I'll wait till monday to see if anyone else has anything to contribute before assigning the points.

    thx again.
    LVL 15

    Expert Comment


    not really unless you want to create the UDFs within the procedure and then drop them afterwards, but that seems kind of a waste of time.

    Not a lot of overhead to keeping them on the server, plus you have an execution plan already built, etc.

    Just give them a name with a specific prefix (e.g. fn_TreatmentSumCharges, fn_Tratment...) to keep them grouped together.

    I've got LOTS of single use procedures and functions. The nice thing about SQL Server is that it lets you do that and it makes life a lot easier.

    Have a good weekend.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now