Procedures in SQL Server – SQL Server Tutorial

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  :-

  1. Input
  2. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *