?
Solved

optiona parameter in stored procedure

Posted on 2014-04-30
3
Medium Priority
?
327 Views
Last Modified: 2014-04-30
Hello,

I have to add a parameter to a stored procedure that will be passed from a ssrs report.

The parameter in the report will be a textbox with the option of being null and/ or blank.

I would like to see if I can get an example of how I can deal with the situation where the user does not enter a parameter, what the WHERE / AND condition look like?

WHERE OrderNumber = @OrderNumber <-- what if they pass null? how can I code around that?

Thank you much in advance.
0
Comment
Question by:metropia
[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
  • 2
3 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40032706
I usually do this...
CREATE PROC your_proc (@OrderNumber int = NULL) as 
...
WHERE (OrderNumber = @OrderNumber OR @OrderNumber IS NULL) 

Open in new window

0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 40032711
If your data set is extremely large, it may be worth splitting your query in two..
CREATE PROC your_proc (@OrderNumber int = NULL) as 
...
IF @OrderNumber IS NULL
  begin 
  SELECT ... without WHERE
  end 
ELSE 
  begin 
  SELECT ... WHERE OrderNumber = @OrderNumber 
  end 

Open in new window

0
 

Author Closing Comment

by:metropia
ID: 40032871
Thank you Jim!
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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Suggested Courses

752 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