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

 

 

SQL Server EXISTS

The "EXISTS" condition is used to test the existence of specific record in a sub query.

Syntax:

    WHERE EXISTS ( subquery );  

The sub query also is a SELECT statement .The EXISTS operator returns true if the sub query returns one or multiple records.

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

How EXISTS query is written

SELECT Column1,Column2,Column3..

FROM table_name

WHERE EXISTS

      (SELECT column_name FROM table_name WHERE condition);

  

Example of exists condition . Query return height more than 15.

SELECT a.name, 

       a.age, 

       a.adress,

       a.date_of_birth

FROM student a

WHERE EXISTS

(SELECT b.age FROM student b WHERE b.height>15 and a.age=b.age);

name     age   adress                date_of_birth

John10    21    43,student street    2018-10-08 00:00:00.000
John11    22    44,student street    2018-11-08 00:00:00.000