Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 22nd, 2005, 12:18 PM
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Server Stored Procedure

Is there a better way of writing this i.e. dynamically generating the WHERE clause within the procedure depending on the parameters values passed in? Thanks for your help

"CREATE PROCEDURE dbo.ShiftsBySurgery (@surgeryID as int) AS

    if (@surgeryID > 0)
        SELECT dbo.Area.area, dbo.Rota.rotaName, RTRIM(dbo.Users.forename) + ' ' + RTRIM(dbo.Users.surname) AS doctorName,
                         dbo.DoctorShift.shiftDate, '£' + CAST(dbo.DoctorShift.hourlyRate AS VARCHAR) AS hourlyRate, '£' + CAST(dbo.DoctorShift.adjustment AS VARCHAR)
                              AS adjustment, dbo.DoctorShift.adjustmentComments, dbo.fn_LZ(2, dbo.DoctorShift.startShiftHrs) + ':' + dbo.fn_LZ(2, dbo.DoctorShift.startShiftMins)
                              AS startTime, dbo.fn_LZ(2, dbo.DoctorShift.endShiftHrs) + ':' + dbo.fn_LZ(2, dbo.DoctorShift.endShiftMins) AS endTime, dbo.Surgery.name
            FROM dbo.Surgery INNER JOIN
                          dbo.Doctor ON dbo.Surgery.surgeryID = dbo.Doctor.surgeryID INNER JOIN
                              dbo.Users INNER JOIN
                              dbo.DoctorShift ON dbo.Users.userID = dbo.DoctorShift.gmcCode ON dbo.Doctor.gmcCode = dbo.Users.userID INNER JOIN
                              dbo.Area INNER JOIN
                              dbo.Rota ON dbo.Area.trustID = dbo.Rota.trustID AND dbo.Area.areaID = dbo.Rota.areaID ON dbo.DoctorShift.trustID = dbo.Rota.trustID AND
                              dbo.DoctorShift.areaID = dbo.Rota.areaID AND dbo.DoctorShift.rotaID = dbo.Rota.rotaID
            WHERE dbo.DoctorShift.cancellationID IS NULL AND dbo.Surgery.surgeryID = @surgeryID
            ORDER BY dbo.DoctorShift.shiftDate, starttime, endtime
    else
        SELECT dbo.Area.area, dbo.Rota.rotaName, RTRIM(dbo.Users.forename) + ' ' + RTRIM(dbo.Users.surname) AS doctorName,
                         dbo.DoctorShift.shiftDate, '£' + CAST(dbo.DoctorShift.hourlyRate AS VARCHAR) AS hourlyRate, '£' + CAST(dbo.DoctorShift.adjustment AS VARCHAR)
                              AS adjustment, dbo.DoctorShift.adjustmentComments, dbo.fn_LZ(2, dbo.DoctorShift.startShiftHrs) + ':' + dbo.fn_LZ(2, dbo.DoctorShift.startShiftMins)
                              AS startTime, dbo.fn_LZ(2, dbo.DoctorShift.endShiftHrs) + ':' + dbo.fn_LZ(2, dbo.DoctorShift.endShiftMins) AS endTime, dbo.Surgery.name
            FROM dbo.Surgery INNER JOIN
                          dbo.Doctor ON dbo.Surgery.surgeryID = dbo.Doctor.surgeryID INNER JOIN
                              dbo.Users INNER JOIN
                              dbo.DoctorShift ON dbo.Users.userID = dbo.DoctorShift.gmcCode ON dbo.Doctor.gmcCode = dbo.Users.userID INNER JOIN
                              dbo.Area INNER JOIN
                              dbo.Rota ON dbo.Area.trustID = dbo.Rota.trustID AND dbo.Area.areaID = dbo.Rota.areaID ON dbo.DoctorShift.trustID = dbo.Rota.trustID AND
                              dbo.DoctorShift.areaID = dbo.Rota.areaID AND dbo.DoctorShift.rotaID = dbo.Rota.rotaID
            WHERE dbo.DoctorShift.cancellationID IS NULL
            ORDER BY dbo.DoctorShift.shiftDate, starttime, endtime
GO


 
Old February 22nd, 2005, 12:52 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

It looks like you pass in 0 when you want all surgeries - if you can change this to pass in NULL in that case then you can do away with the if ... else ... and just change your WHERE clause to:

WHERE dbo.DoctorShift.cancellationID IS NULL AND dbo.Surgery.surgeryID = COALESCE(@surgeryID, dbo.Surgery.surgeryID)

The COALESCE function returns the first non-NULL argument, so if @surgeryID is NULL then you have
WHERE ... AND dbo.Surgery.surgeryID = dbo.Surgery.surgeryID
so you get all surgeries (i.e. @surgeryID is ignored)

but if @surgeryID is non-NULL you have
WHERE ... AND dbo.Surgery.surgeryID = @surgeryID
so you only get the surgeries you want

hth
Phil
 
Old February 22nd, 2005, 03:04 PM
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks thats a lot neater than the original and should be more useful when using multiple filters passed in as parameters.

Cheers

Doug.





Similar Threads
Thread Thread Starter Forum Replies Last Post
ASP.NET & SQL Server 2K Stored Procedure kwilliams ASP.NET 2.0 Basics 7 May 10th, 2006 12:55 AM
No 'New Stored Procedure" in server explorer jkusmanto VS.NET 2002/2003 0 May 3rd, 2006 03:24 PM
Sql server Stored procedure ranakdinesh BOOK: Professional C#, 2nd and 3rd Editions 2 May 29th, 2004 12:08 AM
SQL Stored Procedure... babloo81 SQL Server ASP 1 December 8th, 2003 03:38 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.