Better way of writing this ms sql query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Better way of writing this ms sql query

Author

Topic

DesiGal
Starting Member

31 Posts

Posted - 2009-08-25 : 12:09:17

consider this stored procedure in SQL server 2005

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[P_GetValues]
(

@StartDate datetime,
@EndDate datetime,
@Name nvarchar(255),
@LocationId int,
@CostCenterId int,
@EquipmentTypeId int
)
AS

BEGIN
SET NOCOUNT ON;

--When none of the parameters was supplied

--block start

IF @StartDate = '1/1/1900 12:00:00 AM' AND @EndDate = '1/1/1900 12:00:00 AM' AND @LocationId = 0 AND @CostCenterId = 0 AND @Name = 'empty' and @EquipmentTypeId = 0

BEGIN
SELECT tableA.CloseDate, tableA.Deleted, tableA.tableAId, tableA.tableANumber, tableA.SegId, tableA.OpenDate, tableA.PointOfContact, tableA.ShipperId, tableA.SerialNumberIn, tableA.BI, tableA.BO, tableA.DeletedOn, tableA.DeletedBy, Comments.Description, EquipmentTypes.Type, Makes.Company, Models.ModelName, Equipment.SerialNumber, CostCenters.CostCenter ,Facilities.Facility, SA.FacilityId, SA.CostCenterId, Shippers.ShipperName
FROM tableA

INNER JOIN tableA_Comments ON tableA.tableAId = tableA_Comments.tableAId

INNER JOIN Comments ON tableA_Comments.CommentId = Comments.CommentId

INNER JOIN Equipment ON tableA.EquipmentId = Equipment.EquipmentId

INNER JOIN Models ON Equipment.ModelId = Models.ModelId INNER JOIN Makes ON Models.MakeId = Makes.MakeId
INNER JOIN EquipmentTypes ON Models.EquipmentTypeId = EquipmentTypes.EquipmentTypeId

INNER JOIN SA ON tableA.SegId = SA.SegId
INNER JOIN Facilities ON SA.FacilityId = Facilities.FacilityId INNER JOIN CostCenters ON SA.CostCenterId = CostCenters.CostCenterId
INNER JOIN Shippers ON tableA.ShipperId = Shippers.ShipperId

WHERE (tableA.CloseDate IS NULL) AND (tableA.Deleted = 0)
order by tableA.OpenDate

--block end

this block is repeated several times with a different if and where condition

As you can see its a huge query.Repeating the select query for every condition(whether that parameter was supplied or not) is kind of annoying simply changing the if and where condition and the rest of the query is same.Is there a better way of doing this.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-25 : 12:53:52

First:
The comment "--When none of the parameters was supplied" means that the calling app is giving standard values for the parms like to see in the IF-Statement?

Second:
It would be easier to show the query where the parms are in use.
Maybe we can build a combined query without needing IF-Statement around.

No, you're never too old to Yak'n'Roll if you're too young to die.

DesiGal
Starting Member

31 Posts

Posted - 2009-08-25 : 13:27:55

My procedure accepts six parameters startDate,endDate,Name,LocationId,CostcenterId,EquipmentTypeId.

This procedure can be called without any parameters

IF @StartDate = '1/1/1900 12:00:00 AM' AND @EndDate = '1/1/1900 12:00:00 AM' AND @LocationId = 0 AND @CostCenterId = 0 AND @Name = 'empty' and @EquipmentTypeId = 0

the above values are default values
in this case i simply execute the select statement

if the procedure is called with an equipmentTypeId the if condition would be

IF @StartDate = '1/1/1900 12:00:00 AM' AND @EndDate = '1/1/1900 12:00:00 AM' AND @LocationId = 0 AND @CostCenterId = 0 AND @Name = 'empty' and @EquipmentTypeId <> 0

and i will have the same select statement with a where clause

where EquipmentTypeId=@EquipmentTypeId

What is a combine query?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-25 : 13:32:29

What I mean with combined query is as follows.
If your parms are coming in with NULL-values instead of default values then maybe you need only ONE select statement without using that if...

for example:
where @EquipmentTypeId IS NULL OR EquipmentTypeId=@EquipmentTypeId

No, you're never too old to Yak'n'Roll if you're too young to die.

DesiGal
Starting Member

31 Posts

Posted - 2009-08-25 : 14:13:21

Awesome......Why didn't I think of this before.
Thanks webfred

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-25 : 14:14:35

welcome