Solved

optiona parameter in stored procedure

Posted on 2014-04-30
3
321 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 65

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 65

Accepted Solution

by:
Jim Horn earned 500 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

739 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