[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

optiona parameter in stored procedure

Posted on 2014-04-30
3
Medium Priority
?
333 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 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

Independent Software Vendors: 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

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…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

834 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