Sub Programs and Stored Procedures in Sql Server

Sub Programs

 Annonymos blocks can be executed at a point of time on a particluar machine and if we want to execute those blocks from other systems.we need to execute those blocks from other systems we need to explicitly write those programs wherever we want to over come these problems we use sub programs

 -> A sub program is a named block of code.which is saved on the database server and can be executed any time from any machine connected to the server

 -> A sub programs are refer with different names like C ,C++ languages, Methods in Java, Dotnet languages, Where as under databases we call them as stored procedurce or stored functions


Stored Procedure:  *** very very Important concept ***
----------------- -------------------------------------

 Stored Procedure is a block of code that performs an action whenever it is called.


  Syntax :

   Create | Alter Procedure [(@ [size] [=default] [Out | Output ], [........n])]      [with ]

           As

           Begin

            -stmts

           End


  - > Create is used for creating a new procedure where alter is used for modifing an existing procedure.

  - > Name of the procedure is used defined which must be unique under the database.

  - > If required we can pass parameters to a procedure by passing parameters is only optional.

  - > Parameters can be either input or output parameters.where input parameters used for bringing a value into the procedure for execution.Where as output parameters       are used for taking or carring a value out of procedure ' after ' execution.

  - > The Default parameter type is input and to declare a parameter as output .we need to use out , output keywords beside the parameters


A procedure is devided into two parts :
---------------------------------------

  1) Procedure header

  2) Procedure body

  The content above  " AS " is procedure header and content below  " AS " is procedure body.

  If procedure is created it will only saved on the database server as an object, if we want it to be executed.it must be called explicitly as following

Calling A Stored Procedure:
---------------------------

 Exec  [ ]

 How to create a Stored Procedure :
----------------------------------

Example 1 : 

 Calling or Executing the procedure
 ---------------------------------
   
    Create Procedure TestSP1

        As

        Begin

        print 'My first-stored procedure'

        End

 Output: My first-stored procedure

 Exec TestSP1

Example 2 :

      Create Procedure TestSP2(@x int, @y int)

       As

       Begin
     
         Declare @z int

         set @z=@x+@y

        print 'Sum of the given no is : ' +Cast(@z as varchar)

       End

 Calling or Executing the parameters above procedure
 ----------------------------------------------------

        Exec TestSP2 100,50

        Exec TestSP2 120,800


About Author: author 4 + years of Information Technology experience in understanding and analyzing the software and hardware engineering requirements, onsite and offshore management of product development. Expert work on design and development for windows,web,mobile Expert knowledge of C#,ADO.NET, ASP.Net MVC, ENTITY FRAMEWORK, LINQ,COLLECTIONS, JAVASCRIPT,AngularJs 1.0,Angular2, Jquery,Jquery-Ajax, SQL SERVER, XAML,Windows phone 8.1 Good knowledge of WCF-Restful services,WCF, WEB API-Services ,Web- Services,MIcrosoft-Azure,JUICE UI,HTML,CSS,AJAX Read More...

Join him on Google+ | Facebook | Linkedin

No comments