Avatar of CipherIS
CipherIS
Flag for United States of America asked on

SQL Where Cluase - Use Parameter if passed

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

Microsoft SQL Server 2008Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
CipherIS

8/22/2022 - Mon
Lee

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
CipherIS

ASKER
was hoping there was another solution as my SELECT statement is quite long
ASKER CERTIFIED SOLUTION
Daniel Wilson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Lee

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Jim Horn

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)
CipherIS

ASKER
@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?
CipherIS

ASKER
It works - was a typo on my end.  Thx
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
CipherIS

ASKER
Works like a charm!