Solved

SQL Where Cluase - Use Parameter if passed

Posted on 2012-12-20
8
311 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
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

809 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