Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

optiona parameter in stored procedure

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
metropia
Asked:
metropia
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I usually do this...
CREATE PROC your_proc (@OrderNumber int = NULL) as 
...
WHERE (OrderNumber = @OrderNumber OR @OrderNumber IS NULL) 

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
metropiaAuthor Commented:
Thank you Jim!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now