Solved

optiona parameter in stored procedure

Posted on 2014-04-30
3
307 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
  • 2
3 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you Jim!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now