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!
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
Do more with
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
@ID as INT = null
AS
BEGIN
IF @ID is not null
SELECT *
FROM MyTable
WHERE ID = @ID
ELSE
SELECT *
FROM MyTable
END
CREATE PROC [dbo].[mySPROC]
@ID as INT = null
AS
BEGIN
SELECT *
FROM MyTable
WHERE ID = @ID OR @ID is NULL
END
@ID as INT = null
AS
BEGIN
SELECT *
FROM MyTable
WHERE ID = @ID OR @ID is NULL
END
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.
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)
@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?
Premium Content
You need an Expert Office subscription to comment.Start Free Trial