Variables within as MS SQL query?

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.
Who is Participating?
dbbishopConnect With a Mentor Commented:
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

No, i don't believe so.

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

select tre1.*, myfunction(x) .....
Aneesh RetnakaranDatabase AdministratorCommented:
you can't
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Alan Huseyin KayahanCommented:
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


Alan Huseyin KayahanCommented:
sorry wrong question
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
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.

--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:
--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
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
csalmondAuthor Commented:
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.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.