?
Solved

SQL Where Cluase - Use Parameter if passed

Posted on 2012-12-20
8
Medium Priority
?
315 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 2000 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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 66

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

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…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

741 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