SQL query help(better way of writing this)

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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL query help(better way of writing this)

Author

Topic

DesiGal
Starting Member

31 Posts

Posted - 2009-10-16 : 11:58:07

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-16 : 12:05:07

Please try it in only one statement, but I am not sure:
where LD.Deleted = @Deleted
and ((LD.CloseDate is null and @Closed=0) or (LD.CloseDate is not null and @Closed=1))

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

DesiGal
Starting Member

31 Posts

Posted - 2009-10-16 : 12:27:17

Thanks..that works

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources