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
Microsoft SQL Server 2008Microsoft SQL ServerMicrosoft SQL Server 2005
Last Comment
CipherIS
ASKER
was hoping there was another solution as my SELECT statement is quite long
ASKER CERTIFIED SOLUTION
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
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
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
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)
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)
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.
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?
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
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?
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.
ASKER
Works like a charm!
@ID as INT = null
AS
BEGIN
IF @ID is not null
SELECT *
FROM MyTable
WHERE ID = @ID
ELSE
SELECT *
FROM MyTable
END