• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7648
  • Last Modified:

Variables within as MS SQL query?

Is it pobbile to us a variable in a MS SQL query like this:

DECLARE @ChrgTot MONEY

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.
thx,
-Chris.
0
csalmond
Asked:
csalmond
1 Solution
 
derekkrommCommented:
No, i don't believe so.

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

select tre1.*, myfunction(x) .....
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you can't
0
 
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

Regards,

MrHusy
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.

 
Alan Huseyin KayahanCommented:
sorry wrong question
0
 
appariCommented:
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
0
 
dqmqCommented:
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
AS
(
--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
AS
--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
0
 
dbbishopCommented:
create function dbo.GetTotal(@id int)
returns money
as
begin
return
(
SELECT SUM(Amt) FROM Charges WHERE TretId = @id
)
end


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

0
 
dbbishopCommented:
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
0
 
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.
-Chris.
0
 
dbbishopCommented:
Chris,

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.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now