SQL Server Stored Procedure

Stored Procedure do a particular task a relational database management system .Stored Procedure contain group of statement execute in proper order.Generally Stored Procedure is used for insert , update , delete data to the database .Stored Procedure  is a part of a database system which is very fast in execution, as it is per-compiled and execution path is predefined. Stored Procedure is called by on or more application connected to the database. Stored Procedure support multiple  call. Stored Procedure accept input parameter, support output parameter and parameter accept value as null also.

Syntax :

USE Database_Name

GO

CREATE PROCEDURE StoreProcedureName

AS

/* Stament /query */

GO

Here , we have created a table and inserted data to the table for example purpose.

CREATE TABLE student

(

          id             INT NOT NULL,

          name           VARCHAR (500) NOT NULL,

          age            INT NOT NULL,

          adress         VARCHAR (500) NOT NULL,

          date_of_birth  DATETIME NOT NULL,

          height         DECIMAL(10,2) NOT NULL

)

                INSERT INTO student(id,name,age,adress,date_of_birth,height)

                 SELECT 1,'John1',12,'34,student street','01/08/2018',10.5

                 UNION

                 SELECT 1,'John2',13,'35,student street','02/08/2018',11

                 UNION

                 SELECT 1,'John3',14,'36,student street','03/08/2018',11.5

                 UNION

                 SELECT 1,'John4',15,'37,student street','04/08/2018',12

                 UNION

                 SELECT 1,'John5',16,'38,student street','05/08/2018',12.5

                UNION

                SELECT 1,'John6',17,'39,student street','06/08/2018',13.5

                UNION

                SELECT 1,'John7',18,'40,student street','07/08/2018',14

                UNION

                SELECT 1,'John8',19,'41,student street','08/08/2018',14.5

                UNION

               SELECT 1,'John9',20,'42,student street','09/08/2018',15

               UNION

               SELECT 1,'John10',21,'43,student street','10/08/2018',15.5

               UNION

               SELECT 1,'John11',22,'44,student street','11/08/2018',16

1)Here is a example of Stored Procedure.

 

CREATE PROCEDURE MyStoreProcedure

AS

    SELECT TOP 5 * FROM student

GO

The above store procedure name is "MyStoreProcedure", it will return top 5 rows from  student table when executed.Below is the syntax for execution.

Execute MyStoreProcedure

Output:
id   name    age   adress                    date_of_birth                     height
1    John1    12     34,student street    2018-01-08 00:00:00.000    10.50
1    John10   21    43,student street    2018-10-08 00:00:00.000    15.50
1    John11   22    44,student street    2018-11-08 00:00:00.000    16.00
1    John2    13    35,student street     2018-02-08 00:00:00.000    11.00
1    John3    14    36,student street      2018-03-08 00:00:00.000   11.50

2)Here is a example of Store Procedure with Multiple Parameter

CREATE PROCEDURE MyStoreProcedure

      @in_age INT = NULL,

      @in_name VARCHAR(50) = NULL

AS

SELECT * FROM student WHERE age=@in_age OR name=@in_name

GO

The above srored procedure accept parameter age and name.Retun 
only those record which match.Here is the execution of the procedure.
 

Execute MyStoreProcedure 12,'John5'

Output:
id   name   age   adress               date_of_birth              height
1  John1    12    34,student street    2018-01-08 00:00:00.000    10.50
1  John5    16    38,student street    2018-05-08 00:00:00.000    12.50


3)Here is a example of Store Procedure with Output Parameter
 

CREATE PROCEDURE MyStoreProcedure

             @in_age INT= NULL,

             @in_age_in_month INT= NULL OUTPUT

AS

SELECT @in_age_in_month=@in_age*12 FROM student WHERE age=@in_age

GO

The above procedure accept age and another variable declare for output.When execute query,the variable(@out_age_in_month) fill will data from stored procedure and print the result.

DECLARE @out_age_in_month INT

Execute MyStoreProcedure 12,@out_age_in_month OUT

PRINT(@out_age_in_month)

Output:

144