Post
Ask Question
Desi Gal

Desi Gal

  • NA
  • 35
  • 0

Better way of writing this query

Oct 16 2009 12:01 PM

Posted - 10/16/2009 :  11:58:07  Show Profile  Email Poster  Edit Topic  Reply with Quote  Delete Topic  Nuke Spam!
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Demo]
(

@Closed bit,
@Deleted bit
)
AS

BEGIN
SET NOCOUNT ON;

IF @Closed = '0'
BEGIN
SELECT LD.LDId, LD.DeviceType, LD.CloseDate,
LD.SId, LD.P#, LD.VdId, LD.Person, LD.RId,
LD.Deleted, LD.DeletedOn, LD.DeletedBy, Device.MdId,
Device.S#, Md.MkId, Md.DeviceTypeId

FROM LD INNER JOIN
Device ON LD.DeviceId = Device.DeviceId
INNER JOIN
Md ON Device.MdId = Md.MdId INNER JOIN
Mk ON Md.MkId = Mk.MkId INNER JOIN
DeviceTypes ON Md.DeviceTypeId =
DeviceTypes.DeviceTypeId INNER JOIN
SA ON LD.SId = SA.SId
WHERE
(LD.CloseDate IS NULL) AND
(LD.Deleted = @Deleted)

END

IF @Closed = '1'
BEGIN
SELECT LD.LDId, LD.DeviceType,, LD.CloseDate,
LD.SId, LD.P#, LD.VdId, LD.Person, LD.RId,
LD.Deleted, LD.DeletedOn, LD.DeletedBy, Device.MdId,
Device.S#, Md.MkId, Md.DeviceTypeId

FROM LD INNER JOIN
Device ON LD.DeviceId = Device.DeviceId
INNER JOIN
Md ON Device.MdId = Md.MdId INNER JOIN
Mk ON Md.MkId = Mk.MkId INNER JOIN
DeviceTypes ON Md.DeviceTypeId = DeviceTypes.DeviceTypeId INNER JOIN
SA ON LD.SId = SA.SId
WHERE
(LD.CloseDate IS NOT NULL) AND
(LD.Deleted = @Deleted)

END
END

same query is repeated with a different where condition

Answers (2)