Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
LoriC
Starting Member
2 Posts |
 Posted - 2006-10-27 : 12:33:29
|
| Hi all,I have an sProc that has one required parameter and one optional paramter. Both are datatype int. The required one is easy, but I am struggling with the optional part. 2 questions:1. In the CREATE, how do I set up the optional int param? CREATE PROCEDURE spMyProc (@required_param int, @optional_param int (not sure what goes here)2. In the WHERE, how do I set it up so if @optional_param has a value, use it, if not, give me all values - i.e.WHERE required_field = @required_param AND optional_field = @optional_param (if it contains a value) OR optional_field > 0 (if optional_param is null).Hopefully that makes sense. Thanks in advance for any advice! |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-10-27 : 13:52:41
|
| create proc spMyProc@required_param int@optional_param int = nullasselect * from tablewhere required_field = @required_param and ((@optional_param is null and optional_field > 0) or (@optional_param is not null and optional_field = @optional_param))Jay White |
 |
|
|
LoriC
Starting Member
2 Posts |
Posted - 2006-10-27 : 14:06:01
|
| Perfect Jay - thanks SO MUCH for the quick response!! |
|
|
|
|
|
|
Optional Parameter