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 ]





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



        print 'My first-stored procedure'


 Output: My first-stored procedure

 Exec TestSP1

Example 2 :

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


         Declare @z int

         set @z=@x+@y

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


 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