Declaring parameters with default values :-
If parameters are declared with default value at the time of calling procedure if the value is not passed to the parameter then default value assigned to that parameter.
Example :-@x int =10
Example :-
alter procedure
Addnum (@x int,@y int=20,@z int output)
As
Begin
Set @z=@x+@y
End.
Declare @ k int
Excute addnum 10,default,@k output
Print @k
Note :- In procedures we have 10 default values,int that case we go for named position and execute as follows:-
Declare @a int, @b int, @k int
Set @a=10
Execute addnum @x=@a,@z=@k output
Print @ k.
Create a procedure to increment particular employee salary by particular percentage :-
Create procedure
Updsal(@e int,@p int,@s smallmoney output)
As
Begin
Update emp set sal=sal+(sal*@p/100)
Where empno=@e
Select @s=sal from emp where empno=@e
And.
- Write a program to I/P employee number and percentage and increment the salary by that %
- After increment if salary exceeds 5000 then cancel that update.
Set implicit_transaction on
Declare @eno int @pen int, @sal smallmoney
Set @eno=7566
Set @pen=70
Execute updsal @eno,@pen,@sal output.
If @sal>5000
Rollback
Else
Commit.