Solved

SQL Where Cluase - Use Parameter if passed

Posted on 2012-12-20
8
313 Views
Last Modified: 2012-12-20
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

0
Comment
Question by:CipherIS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 38709856
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
0
 
LVL 1

Author Comment

by:CipherIS
ID: 38709878
was hoping there was another solution as my SELECT statement is quite long
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 38709881
CREATE PROC [dbo].[mySPROC]
             @ID as INT = null

AS
BEGIN
      SELECT *
        FROM MyTable
        WHERE ID = @ID OR @ID is NULL
END
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 25

Expert Comment

by:Lee Savidge
ID: 38709909
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.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38709943
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)
0
 
LVL 1

Author Comment

by:CipherIS
ID: 38710003
@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?
0
 
LVL 1

Author Comment

by:CipherIS
ID: 38710013
It works - was a typo on my end.  Thx
0
 
LVL 1

Author Closing Comment

by:CipherIS
ID: 38710019
Works like a charm!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question