Procedures in SQL Server :-
- A procedure is a pre-compiled named block stored in database that performs a task & may (or) mayn’t return a value.
- Procedures are stored in database, so it is called “stored procedures”
- Procedures are created to perform DML operations over database [transactions].
Syntax for creating procedure :—
Create/alter procedures <name> → procedure declaration
(Parameters)
As
Begin
Statements → procedure body
End.
Parameters are two types :–
- Formal
- Actual
Example :-
k= add(a,b)
Int add(int x,int y)
{
Return x+y;
}
parameters which are declared in procedures declaration that called “formal parameter”
Parameters which are passed at the time of calling procedure are called “actual parameter”.
Formal & actual parameters are two types :-
- Input
- Output
Input :-
- Input parameters are always receive value in a program and it is a default and it is read only.
Output :-
- output parameters are always sends value to main program.
- And it is write only
Example for input Parameter :-
Create a procedure to add two numbers :-
Create procedures addnum(@x int,@y int)
As
Begin
Declare @z int
Set @ z=@x+@y
Print @z
End
- Once procedure is created the procedures can be executed from [SSMS] SQL Server management studio (or) .net application (or) java application etc.
- In SSMS, procedures can be executed in two ways:
(I) Positional notation
(II) Named notation
(I) Positional notation in SQL :-
- In positional rotation parameters are mapped through their position.
Example: (how to execute the above procedure) in positional notation.
| Execute addnum 50,30 |
(II) Named notation in SQL :-
- In named notation the parameter are mapped through their names.
Example :- {how to execute the above procedure in named notation}
Declare @a int,@b int
Set @ a=40
Set @ b=60
Execute addnum @y=@b,@x=a(or)
@x=@a,@y=@b
Output parameter example :-
Alter procedure
Addnum(@x int,@y int,@z int output)
As
Begin
Set @z=@x+@y
End
How to execute :-
Declare @a int,@b int,@c int
Set @a=30
Set @b=40
Execute addnum @a,@b,@c output
Print @c.