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 |
|
lucasite
Starting Member
4 Posts |
 Posted - 2004-12-01 : 11:05:29
|
| Hi there,is there such a thing as having optional parameters.My example is this: my application presents a user with a form full of fields, EG: firstname, surname, address1, address2, town, county, postcode.In one scenario, the user is adding a record, but only fills in 2 or 3 fields. Passing this new record to the database wouldnt be a problem, as I could use null or default values for the fields that were not filled in.However in a second scenario, in which all fields contain data, but the user modifies only 1 or 2 fields. I understand I could send the full set again as follows:UPDATE CLIENTS SET FirstName=@Fname, Surname=@sName, Address1=@Addr1, etc etcbut is there an easier way rather than coding all this.Any response gratefully appreciated.Andy |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-01 : 11:17:07
|
| Yescreate proc a@i int = 0 ,@s varchar(20) = 'asdf' as...exec a @i = 1the parameters with defaults which aren't set will use the default.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
lucasite
Starting Member
4 Posts |
Posted - 2004-12-01 : 11:23:00
|
| Thankyou,Will give this a try.Andy |
|
|
|
dsdeming
479 Posts |
Posted - 2004-12-01 : 11:27:59
|
| FWIW, if your sp has both required and optional parameters, the required parameters should come first unless you always intend to call the sp with parameter name assignments ( @i = 1 ).create proc test@a int,@i int = 0as...could be called:exec test 7exec test 7, 3exec test @a = 7exec test @a = 7, @i = 3Dennis |
|
|
|
|
|
|
|
|
Optional Parameters in Stored Procedures