What do you mean by stored procedures? What are its importances? Explain with an example.

Stored Procedures

A stored procedure (or stored query as it's sometimes called) is a predefined SQL query stored on the database.

Why should we create and use a stored procedure instead of just creating a SQL string? Well, there are several reasons:

A stored procedure is compiled by the database. This produces an execution plan, so the database knows exactly what it's going to do. This makes the execution of the procedure faster.
Stored procedures are often cached by the database, thus making them faster to run, as they don't have to be read from disk.
You can make your data a little bit more secure by specifying that your database tables can be modified only by stored procedures.
You avoid cluttering your ASP code with lengthy SQL statements. This makes the ASP code easier to maintain.
You can keep all of the SQL code together, on the server.
You can use output parameters in a stored procedure, which allows you to return both a recordset and other values.

Syntax for creating stored procedure:

--Creating stored procedure

CREATE PROCEDURE SP_Name

BEGIN

END

--Creating stored procedure

CREATE PROCEDURE SP_UPDATETABLE

BEGIN

UPDATE STUDENT_INFO

SET NAME='HARI' WHERE ROLLNO=102

END

Computer Science and Information Technology