December 31, 2007 at 4:20 am
Hello everyone, here is my SP:
Create Procedure Test
@Criteria int
As
if (@Criteria =1)
begin
-- All Categories
SELECT dbo.TollingVehicleCategories.TollingVehicleCategoryName, COUNT(t1.TransactionID) AS TotalVehicles,SUM(CASE WHEN TollDiscountID = 4 THEN 1 ELSE 0 END) as VehiclesExempt ,
SUM(CASE WHEN TollDiscountID not in(1,4) THEN 1 ELSE 0 END) as VehiclesDiscounted,SUM(Case WHEN TollDiscountID = 1 THEN 1 ELSE 0 END) as VehiclesCharged, SUM(t1.ChargedAmount) as TollCollected
from Transactions t1 INNER JOIN
dbo.TollingVehicleCategories ON t1.VehicleCategoryCode = dbo.TollingVehicleCategories.TollingVehicleCategoryCode AND
t1.PlazaCode = dbo.TollingVehicleCategories.PlazaCode AND
t1.ProjectCode = dbo.TollingVehicleCategories.ProjectCode AND t1.ClientCode = dbo.TollingVehicleCategories.ClientCode
where t1.shiftid =106
and t1.paymentmodecode = 1
GROUP BY dbo.TollingVehicleCategories.TollingVehicleCategoryName
end
else if (@Criteria =2)
Begin
-- All Operators
SELECT t1.shiftsystemuserid, COUNT(t1.TransactionID) AS TotalVehicles,SUM(CASE WHEN TollDiscountID = 4 THEN 1 ELSE 0 END) as VehiclesExempt ,
SUM(CASE WHEN TollDiscountID not in(1,4) THEN 1 ELSE 0 END) as VehiclesDiscounted,SUM(Case WHEN TollDiscountID = 1 THEN 1 ELSE 0 END) as VehiclesCharged, SUM(t1.ChargedAmount) as TollCollected
from Transactions t1
where t1.shiftid =106
and t1.paymentmodecode = 1
GROUP BY t1.shiftsystemuserid
end
else
Begin
-- All Booths
SELECT t1.BoothCode, COUNT(t1.TransactionID) AS TotalVehicles,SUM(CASE WHEN TollDiscountID = 4 THEN 1 ELSE 0 END) as VehiclesExempt ,
SUM(CASE WHEN TollDiscountID not in(1,4) THEN 1 ELSE 0 END) as VehiclesDiscounted,SUM(Case WHEN TollDiscountID = 1 THEN 1 ELSE 0 END) as VehiclesCharged, SUM(t1.ChargedAmount) as TollCollected
from Transactions t1
where t1.shiftid =106
and t1.paymentmodecode = 1
GROUP BY t1.BoothCode
order Byt1.BoothCode asc
end
I m using SQL Server 2000, In my front end using (ASP.Net), i have 3 radio buttons, if user select 1 radio button first part run (If @Criteria=1){All Categories}, if 2nd(AllOperators) then 2 query run and if 3rd (AllBooths)then last
query return. Is there any other way to make 1 query and on the basis of this query my Group by Clause set and also that value comes in my select stmt list.
its work fine but i need 1 query coz all other fields are same and get data from same table , as u see i removed the join in 2nd and 3rd query coz i din't get any data from
that table, so if i used this no problem just cost of query incresed so i removed join part from last 2 queries. For making 1 query can i add join in last 2 or without join its work in
one query.Plz reply me and guide me. Thanx in Advance.
December 31, 2007 at 7:18 am
I would recommend, rather than trying to write a single query, you write three seperate stored procs, and call the one that you need from the front end.
With a stored proc that has multiple different execution paths, you won't get a single cached execution plan that's optimal for all paths. Either you'll get lots of recompiles, or you'll get a cached plan that's optimal for one branch, but slow as molasses for the others.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 31, 2007 at 7:28 am
Yes, you could write this as a single query. It would take a Case statement in the Group By clause. But, I'm with Gail on this one: You're better off writing three separate procs and calling the appropriate one based on which criterion the user wants from the front end.
First, it will perform better, because of execution plan caching. Second, when (inevitably), someone wants a new column added to one of the reports, but not to the others, it will be quite easy to do so. (These look like business intelligence queries, and my experience with those is they last just about 1 meeting, and then the managers using them want something added or modified.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply