Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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


Reply With Quote
  #2 (permalink)  
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
Reply With Quote
  #3 (permalink)  
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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 09:18 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.