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 |
|
dr S.A.f.
Starting Member
13 Posts |
 Posted - 2006-04-13 : 04:58:45
|
| Hello,Is it possible to define optional parameters for a stored procedure?What I want is to create a search with about 8 parameters, but most users will only use 3 or 4, so It would be nice If I could only provide the used parameters in my code. And have sql give the unused parameters a default value (possibly %)thx. |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-13 : 05:07:59
|
| CREATE PROCEDURE MySProc@MustHaveParam1 int,...@MustHaveParam4 int,@OptionalParam5 int = NULL,@OptionalParam6 int = 1234,...@OptionalParam8 int = NULLAS...You will need to provide Parameters 1 - 4 when the you execute the procedure, and you may optionally provide parameters 5 - 8, otherwise they will take their default values. If you only want to provide SOME of the parameters 5 - 8 then explicitly name them. (Good idea to always name the parameters in the procedure call statement to make them future-proof against changes etc.)Kristen |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-04-13 : 05:08:01
|
You can define parameters with Default value Null. And check Where (@param1 is Null or col1=@Param1)and (@param2 is Null or col2=@Param2)and (@param3 is Null or col3=@Param3) MadhivananFailing to plan is Planning to fail |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-04-13 : 05:10:18
|
| Specify the default value to the @parements in the declaration part...If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them. |
|
|
|
dr S.A.f.
Starting Member
13 Posts |
Posted - 2006-04-13 : 05:23:59
|
| It works, well it is to say, when I execute my sp I don't have to add all the parameters, but offcourse when I call the stored procedure from my good friend vb.net I get the error: parameter count doesn't match parameter value count.But now that I know how to put it in the stored procedure, I can search some more in vb to make it work from there too.Thx for the help. |
|
|
|
|
|
|
|
|
Optional parameter