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)



Set @z=@x+@y


 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)



                    Update emp set sal=sal+(sal*@p/100)

                                  Where empno=@e

                      Select @s=sal from emp where empno=@e


  • 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