Source:Web
Declaring Parameters:
Declaring a parameter requires two to four of these
pieces of information:
The
name
The
data type
The
default value
The
direction
The
syntax follows:
@parameter_name
[AS] data type [= default|NULL]
[VARYING] [OUTPUT|OUT]
The
rules for naming parameters are basically the same as those for a variable.
The
data type also matches the rules of a variable.
NOTE:
One special thing in declaring the data type is to remember that, when
declaring a parameter of type CURSOR, you must also use the VARYING and OUTPUT
options. The use of this type of parameter is pretty unusual, but keep it in
mind.
The
default is
the first place you start to see any real divergence from variables.
Whereas variables are always initialized to a NULL value, parameters aren’t.
Indeed, if you don’t supply a default value, the parameter is assumed to be
required, and an initial value must be supplied when the sproc is called or an
error will be generated. To supply a default, you simply add an = sign after
the data type and provide the default value. After you do this, the users of
your sproc can decide to supply no value for that parameter, or they can
provide their own value.
Like
any function or procedure in any other programming language, stored procedures communicate
with applications or clients through parameters. The maximum number of
parameters in a stored procedure is 2,100 (this was significantly increased
from SQL Server 7, which had a maximum of 1,024 parameters per stored
procedure).
Caution:
Be
aware that Books Online incorrectly states in the "Maximum Capacity
Specifications" section
That
the maximum number of parameters in a stored procedure is 1,024.
Once
created, information about stored procedures' parameters is stored in the SYSCOLUMNS
system table (you already know that SYSOBJECTS stores general information and SYSCOMMENTS stores
the code of the stored procedure).
Types of Parameters:
There are two types of parameters, input and
output:
An input parameter
is similar to a variable passed by value.
Therefore, the stored procedure gets a copy of the data and this doesn't affect
the data outside the stored procedure. In other words, if you pass a variable
as a parameter of a stored procedure, and the value of this variable is
modified inside the stored procedure, this doesn't change the value of the
variable outside the stored procedure.
An output
parameter is like a variable passed by reference. Hence,
because the stored procedure gets a pointer to a variable, any changes made to
it are reflected outside the scope of the stored procedure. Using this type of
parameter, a stored procedure can send values back to the calling application.
To take advantage of output parameters, and to distinguish them from input
parameters, the OUTPUT keyword must be specified when creating the stored
procedure, and also when it is executed.
Sample Stored Procedure:
Create procedure #testproc
As
Begin
Select * from #test where id=3
End
Execution:
Exec
#testproc
Result Set:
id name sal
-----------
------------------------------ -----------
3 this number is 3 375
Sample Stored Procedure with INPUT parameter:
Alter
procedure #testproc @idnum int
As
Begin
Select
* from #test where id=@idnum
End
Execution of Sample
stored procedure:
Exec
#testproc 25
OR
Exec #testproc @idnum=25
Result Set:
Id name sal
-----------
------------------------------ -----------
25 this number is 25 3125
The parameter value is required while executing:
Exec
#testproc
Error message:
Server: Msg 201, Level 16, State 3, Procedure
#testproc_________________0004B67B, Line 0
Procedure '#testproc_____0004B67B'
expects parameter '@idnum', which was not supplied.
This is real divergence from variables, for
variables if
we didn’t assign value then NULL value will assign automatically, But this is
not the case in Parameters we should supply the value, if we didn’t specify the
default clause.
Solution (Sample Stored Procedure with INPUT parameter and default value):
Here we are using the default value if we specify
the value while we are executing it will ignore the default value, if we miss
the parameter value this default value will consider.
Alter procedure #testproc @idnum int=25
As
Begin
Select * from #test where id=@idnum
End
Exec #testproc
Result Set:
Id name sal
-----------
------------------------------ -----------
25 this number is 25 3125
Demonstrating modifies INPUT parameter in SP:
Alter procedure #testproc @idnum int=25
As
Begin
Set @idnum=35
Select * from #test where id=@idnum
End
Execution:
Exec #testproc
Result Set:
id
name
sal
----------- ------------------------------
-----------
35
this number is 35
4375
Points To Remember:
- We can modify the value of an INPUT varible with in the procedure body
But this is not the case in oracle in oracle
INPUT parameter is read only parameter.
Sample stored procedure with OUTPUT parameter:
Alter procedure #testproc @idnum int ,@sal int
output
As
Begin
Select @sal=sal from #test where id=@idnum;
Return @sal
End
Execution:
Declare
@salary int
exec #testproc 25,@salary output
print 'The salary of an employee having id 25 is
'+cast(@salary as varchar(10))+' rupees'
Result set:
The
salary of an employee having id 25 is 3125 rupees
Points To Remember:
There
are several things that you should take note of between the sproc itself and
the usage of it by the calling script:
- The OUTPUT keyword is required for the output parameter in the sproc declaration.
- You must use the OUTPUT keyword when you call the sproc, much as you did when you
Declared
the sproc. This gives SQL Server advance warning about the special handling
that
Parameter
will require. Be aware, however, that forgetting to include the OUTPUT keyword
won’t create a
runtime error. You won’t get any messages about it, but the
value for the output
parameter
won’t be
moved into your variable. You wind up with what was already there,
most
likely
a NULL value. This means that you’ll have what I consider to be the most
dreadful of all
computer
terms: unpredictable results.
Demonstrating Importance of OUTPUT keyword in SP calling:
Here
we are ignoring the OUTPUT keyword in the procedure calling. So the value of
the output variable won’t move to the @salary variable hence in the result set
NULL will come.
Declare
@salary
int
Exec
#testproc 25,@salary
print
'The salary of an employee having id 25 is '+cast(@salary as varchar(10))+'
rupees'
Result set:
The salary of an employee having id 25 is rupees
- The variable you assign the output result to does not have to have the same name as the internal parameter in the sproc.
For
example, in the previous sproc, the internal parameter was called @sal but the
variable the value was passed to was called @salary
- The EXEC (or EXECUTE) keyword was required because the call to the sproc wasn’t the first thing in the batch. You can leave off the EXEC if the sproc call is the first thing in a batch, but I recommend that you train yourself to use it regardless.
Advantages of an stored procedure parameters:
The
advantage of using stored procedures is that they can return result sets, using
SELECT statements in the body of the stored procedure to the calling function /
procedure.
Limitations of an Stored procedure parameters:
However,
one of the limitations of using parameters is that you can't use a parameter to
pass the name of a database object (table, column, or stored procedure) to the
stored procedure.
For
this purpose, you must build the query at runtime, generating a dynamic query
(using EXEC or sp_executesql). Notice that this is not a restriction of
parameters; it is a restriction of the Data Definition Language (DDL).
Alter procedure #testproc @tablename varchar (20)
As
Begin
Select * from @tablename
End
Error Message:
Server: Msg 137, Level 15, State 2, Procedure
#testproc, Line 5
Must declare the variable '@tablename'.
Dynamic Query to pass object name as parameter:
Alter procedure #testproc @tablename varchar (20)
As
Declare
@qry Varchar (1000)
Begin
Set @qry='select * from '+@tablename+' where id=20'
Exec (@qry)
End
Execution:
Exec #testproc #test
No comments:
Post a Comment