SQL Where Cluase - Use Parameter if passed

CipherIS
CipherIS used Ask the Experts™
on
Working on writing a SPROC.  I have an ID that can be optionally passed.  If it is passed I want it included in the where clause otherwise I want to get all records.  How can I accomplish this?

CREATE PROC [dbo].[mySPROC]
		 @ID as INT = null

AS
BEGIN
	SELECT * 
        FROM MyTable
        WHERE ID = @ID
END

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
CREATE PROC [dbo].[mySPROC]
             @ID as INT = null

AS
BEGIN
    IF @ID is not null
      SELECT *
        FROM MyTable
        WHERE ID = @ID
    ELSE
      SELECT *
        FROM MyTable
END

Author

Commented:
was hoping there was another solution as my SELECT statement is quite long
CREATE PROC [dbo].[mySPROC]
             @ID as INT = null

AS
BEGIN
      SELECT *
        FROM MyTable
        WHERE ID = @ID OR @ID is NULL
END
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Then you'll need to resort to dynamic SQL but your best bet is to have an if statement in there and do one or the other.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Either of the above will work, alough I typically use DanielWilson's method.

Also, if you have multiple WHERE expressions, make sure you use parenthesis so that it evaluates together, such as..

WHERE customer_id = 1
      AND hell_freezes_over = True
     AND (ID = @id OR @ID IS NULL)

Author

Commented:
@DanielWilson your solution does work.  

I should have added more info but didnt' think I needed to.  Here is the problem I am having with the solution.

CREATE PROC [dbo].[mySPROC]
             @ID as INT = null,
             @dtStart as DATETIME = null,
             @dtEnd as DATETIME = null

AS
BEGIN
      SELECT *
        FROM MyTable
        WHERE (ID = @ID OR @ID is NULL)
        AND ((MyDate >= @dtStart And MyDate <= @dtEnd) 
                   OR (@dtStart IS NULL And @DtEnd IS NULL))
END 

Open in new window


EXEC mySPROC - DOES NOT WORK
EXEC mySPROC '11/1/12', 11/30/12', NULL - DOES NOT WORK
EXEC mySPROC NULL, NULL, 31 - WORKS

Any ideas?

Author

Commented:
It works - was a typo on my end.  Thx

Author

Commented:
Works like a charm!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial