|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
November 9th, 2004, 05:12 AM
|
|||
|
|||
Using Default parameter values for T-SQL stored procedures
Setting default values to stored procedure parameters is quite a useful feature, especially when the same stored procedure is called in different contexts. This saves the programmers from sending some dummy values to the procedure when the parameter is irrelevant in the present context. Minimizing the programmer’s effort by sending only the required parameters to the procedure is not the sole purpose of using default parameters.
When a stored procedure is already in production and some part of your application needs to send additional parameters to enhance the result from the procedure, it is always advisable to use default parameters, so that other part of the application that is happily using this procedure with the existing parameters will not be affected by this change. You are saved from the headache of finding out all the code that uses this procedure and sending dummy values when it is not necessary. Syntax: Create Procedure DefaultParamProcedure ( @Param1 int, @Param2 varchar(20), @Param3 int = NULL) As Begin -- Some queries End Calling the above procedure from another procedure ExeDefaultParamProcedure 1,’xyz’c It is always advisable to use the default parameters at tail of the parameter list so that the actual parameters supplied are passed to the relevant formal parameters. In case if you want to send values to the trailing default parameters and let the leading parameters to use the default values use formal parameter names to assign values to them. Syntax: Create Procedure DefaultParamProcedure ( @Param1 int, @Param2 varchar(20) = ‘xyz’, @Param3 int = NULL) As Begin -- some queries End Calling the above procedure from another procedure Exec DefaultParamProcedure 1, @Param3 = 5 This will assign ‘1’ to @Param1 and ‘5’ to @Param3 and @Param2 will use the default value ‘xyz’ assigned to it. An interesting point is, even the “Output” parameters can have default values. In some cases you may use output parameters to get some values from the stored procedure apart from the result set. For example, the number of records returned by the procedure. Not all the scenarios will need that data. In that case you can simply omit the use of that output parameter by using default values. Syntax: Create Procedure DefaultParamProcedure ( @Param1 int, @Param2 varchar(20) = ‘xyz’, @Param3 int = NULL Output) As Begin -- some queries End Calling the above procedure from another procedure Exec DefaultParamProcedure 1 The same is the case when you are calling the procedure from the front-end code that you use. All you have to do is supply only the required parameters for the procedure. Rest you can simply omit if they are irrelevant in the present call.
|
|
| Viewing: ASP Free Forums > Other > Development Articles > Using Default parameter values for T-SQL stored procedures |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
