Stored Procedure in SQL: Benefits And How to Create It

Published On: 22 March 2023.By mohammed aslam.

Digital Engineering

Stored Procedure in SQL: Benefits And How to Create It

What is a procedure ?

A stored procedure in SQL is a group of SQL statement that are stored together in a database. Based on the statements in the procedure and the parameters you pass, it can perform one or multiple DML operations on the database, and return value, if any. Thus, it allows you to pass the same statements multiple times, thereby, enabling reusability.

Difference Between Store Procedures & Triggers:

Working Flow of Store Procedure:

Syntax to create a procedure:

CREATE PROCEDURE ProcedureName ()

AS

BEGIN

-- SQL statements go here

END

CREATE PROCEDURE ProcedureName ()

BEGIN

-- SQL statements go here

END

DELIMITER &&

create or alter procedure pr_name (p_name varchar, p_age int)

as

declare var_name data type;

Variable;

@v_name int,

@v_age int;

begin

procedure body – all logic

end

DELIMITER;

DELIMITER &&

create or alter procedure pr_name (p_name varchar, p_age int)

declare var_name data type;

Variable;

@v_name int,

@v_age int;

begin

procedure body – all logic

end

DELIMITER;

It has a name, parameter list and sql statement.

The CREATE PROCEDURE statement is used to create the Stored Procedure, ans the AS keyword is used to indicate the start of the procedure’s logic. The SQL Statement that make up the procedure’s logic are placed between the BEGIN and END keywords.

Call Procedure:

To call a procedure we use the call Keyword and then procedure name and paranthesis.

Eg:-

call pr_name();

1	call pr_name();

Parameters:

There may be times where you want to pass information to the stored procedures

1. Create Procedure p()

2. Create Procedure p( [IN] name data-type)

3. Create procedure p( OUT name data-type)

4. Create Procedure p (INOUT name data -type)

Type of Parameters:

IN: It is the default parameter that will receive input value from the program

OUT: It will send output value to the program , the value of an OUT parameter can be changed inside the stored procedure and its new value is passed back to the calling program.

IN OUT: It is the combination of both IN and OUT. Thus, it receives from, as well as sends a value to the program

IN Eg:

Delimiter &&

create procedure p1(in p int)

Begin

declare x int ;

Set x = p;

Select x;

End //

Delimiter &&

create procedure p1(in p int)

Begin

declare x int ;

Set x = p;

Select x;

End //

Call p(123);

1	Call p(123);

OUT Eg:

create procedure p2( OUT p INT)

Begin

Set p = -5;

Select p;

End //

create procedure p2( OUT p INT)

Begin

Set p = -5;

Select p;

End //

Call p2(@o);

1	Call p2(@o);

INOUT Eg:

create procedure p3( INOUT P INT)

Begin

Set P = P*2;

Select p;

End //

create procedure p3( INOUT P INT)

Begin

Set P = P*2;

Select p;

End //

Set @y=5

Call p3(@y);

1	Call p3(@y);

Loops & Statement:

IF-THEN- ELSE

create procedure p3( IN parameter1 INT)

Begin

DECLARE variable1 INT;

SET variable1 = parameter 1+1;

If variable1 =0 then

INSERT INTO t VALUES (17);

END IF;

IF parameter1 = 0 then

INSERT INTO T VALUES (15);

ELSE

INSERT INTO T VALUES(15);

END IF;

END;

create procedure p3( IN parameter1 INT)

Begin

DECLARE variable1 INT;

SET variable1 = parameter 1+1;

If variable1 =0 then

INSERT INTO t VALUES (17);

END IF;

IF parameter1 = 0 then

INSERT INTO T VALUES (15);

ELSE

INSERT INTO T VALUES(15);

END IF;

END;

Case:

create procedure p4( IN parameter1 INT)

Begin

DECLARE variable1 INT;

SET variable1 = parameter 1+1;

CASE Variable1

WHEN 0 THEN INSERT INTO t VALUES (17);

WHEN 1 THEN INSERT INTO t VALUES (18);

ELSE INSERT INTO t VALUES (19);

END CASE;

END;

create procedure p4( IN parameter1 INT)

Begin

DECLARE variable1 INT;

SET variable1 = parameter 1+1;

CASE Variable1

WHEN 0 THEN INSERT INTO t VALUES (17);

WHEN 1 THEN INSERT INTO t VALUES (18);

ELSE INSERT INTO t VALUES (19);

END CASE;

END;

While —–END WHILE:

Create Procedure p5()

Begin

Declare v INT;

SET v = 0;

while v<5 Do

Insert Into t Values (v);

Set v = v+1;

End While;

End://

Create Procedure p5()

Begin

Declare v INT;

SET v = 0;

while v<5 Do

Insert Into t Values (v);

Set v = v+1;

End While;

End://

Variables:

A variable is a name that refers to a value and name that represents a value stored in the computer memory

1. The statements used to define variables

2. Eg: declare a int;

3. variable are declared between the begin and end tag.

4. Scope of variable:

create procedure p()

BEGIN

DECLARE X1 CHAR (5) DEFAULT ‘outer’

BEGIN

DECLARE X1 CHAR(5) DEFAULT ‘inner’;

SELECT X1;

END

SELECT X1;

END;

create procedure p()

BEGIN

DECLARE X1 CHAR (5) DEFAULT ‘outer’

BEGIN

DECLARE X1 CHAR(5) DEFAULT ‘inner’;

SELECT X1;

END

SELECT X1;

END;

5. Call Scope example : call p ()

Example : Create Store Procedure for insert data in student and student fee table if Student_Id take as variable.

Create Store Procedure:

DELIMITER &&

CREATE PROCEDURE get_student (IN Name1 varchar(30), IN Email1 varchar(30), IN Fee1 int)

BEGIN

declare var1 int default 0;

INSERT INTO student (Name,Email) VALUES (Name1 ,Email1 );

SELECT Student_Id into var1 from student where Email=Email1;

INSERT INTO stu_fee ( Student_Id,Fee) VALUES (var1,Fee1);

END &&

DELIMITER ;

DELIMITER &&

CREATE PROCEDURE get_student (IN Name1 varchar(30), IN Email1 varchar(30), IN Fee1 int)

BEGIN

declare var1 int default 0;

INSERT INTO student (Name,Email) VALUES (Name1 ,Email1 );

SELECT Student_Id into var1 from student where Email=Email1;

INSERT INTO stu_fee ( Student_Id,Fee) VALUES (var1,Fee1);

END &&

DELIMITER ;

Call Store Procedure:

call get_student ( 'Mohammed','mohammed@aurigaait.com',300);

1	call get_student ( 'Mohammed','mohammed@aurigaait.com',300);

Output:

Stu_fee table:

How to Modify a Stored Procedure in SQL?

You can modify a stored procedure just like modifying a table in the database using the ALTER command.

ALTER PROCEDURE GetCarDesc

AS

BEGIN

SET NOCOUNT ON

SELECT C.CarID,C.CarName,CD.CarDescription  FROM 

Car C

INNER JOIN CarDescription CD ON C.CarID=CD.CarID

END

ALTER PROCEDURE GetCarDesc

BEGIN

SET NOCOUNT ON

SELECT C.CarID,C.CarName,CD.CarDescription FROM

Car C

INNER JOIN CarDescription CD ON C.CarID=CD.CarID

END

Output:

As you can see, the output shows “Command(s) completed successfully.” Thus, the GetCarDesc procedure that you created earlier is now modified.

How to Rename a Stored Procedure in SQL?

You can rename a stored procedure in SQL using the in-built procedure sp_rename. The syntax for renaming is:

sp_rename ‘old_name’, ‘new_name’

Using the above syntax, we will rename the GetCarDesc stored procedure that we have altered in the above section.

sp_rename 'GetCarDesc', 'New_GetCarDesc';

1	sp_rename 'GetCarDesc', 'New_GetCarDesc';

You can confirm the change by executing the stored procedure with the new name.

CALL New_GetCarDesc();

1	CALL New_GetCarDesc();

Output:

As you can see in the output, it executes the stored procedure successfully, confirming the rename.

SQL Stored Procedures can be executed in a variety of ways, including:

Executing the Stored Procedure from within a SQL script using the EXEC statement.
Calling the Stored Procedure from within an application using an API or library
Scheduling the Stored Procedure to run at a specific time using a SQL Server Agent job.
Creating a trigger that automatically executes the Stored Procedure when a specific event occurs.

Advantages:

Stored procedure increases performance of application.
Stored procedure is secured.
Stored procedure is reusable.
Stored procedure is transparent to any application which wants to use it.
Stored Procedure reduced the traffic between application and database.
Easy to modify with the help of the ALTER TABLE command.

Disadvantages:

Take Memory and CPU usage more.
Difficult to maintain
Difficult to debug.

Conclusion:

SQL Stored Procedures are a powerful feature of any database management system. Help to improve reduce code duplicate, security and efficiency. They provide a way to encapsulating complex SQL queries and logic into a single, reusable entity that can be executed from within the database. Creating and using Stored Procedures is a straightforward process, making them an essential tool for any database administrator.