What is a Stored Procedure? Stored Procedures For Beginners
Stored Procedures: A Brief Description
In modern day applications, almost all applications have a database running in the background that stores information. To store, retrieve and manage the data in the database, queries like SELECT, DELETE, LIKE, GROUP BY, etc. are used. In many cases, similar queries are used to manipulate the database. In such cases, it takes a lot of time for the database to compile the query each time it is passed. To avoid this, stored procedures are used. Stored procedures take less time to return results from the database. Stored procedures use parameters to get values from the application instead of queries. These parameters are then passed to the query present in the stored procedure.
To illustrate the use of stored procedures, an easy to understand example is given below. MS SQL has been supporting stored procedures for a long time. Its competitor MySQL has started supporting stored procedures only in recent versions. The example below is written using MS SQL.
Syntax of Stored Procedures
Skeleton of a Stored Procedure:
Comments in stored procedures begin with a – at the start of the line.
CREATE PROCEDURE procedure_name
(
--List of Parameters go here
)
AS
BEGIN
--all the operations to be performed go here
END
Parameter names in MS SQL stored procedures start with a @ symbol followed by the parameter name and the data type of the values that are passed to the parameter.
Example of a Stored Procedure:
If you are using a MS SQL server, start MS SQL and create the database if you don’t have one.
Once the database is created, create a table on which the operations are to be performed. For this example, I have created a table called names_table that has two columns. The first is the auto generate or the auto increment column that holds the index of entries in the table. I have named it as “id” (without the quotes). The second column is called “names” (without the quotes again). Insert some values into the table and then proceed to create the stored procedure.
To create a stored procedure, click on the database and the tree will expand. Then click on the programmability option and it will expand to show the stored procedures. On the stored procedures folder, right click on it and select the New Procedure option. A blank query page will open in which you can write the stored procedure.
Writing Your First Stored Procedure
In this example, we’ll see how to write and execute a stored procedure and use it for the SELECT and DELETE queries.
CREATE PROCEDURE sp_firstprocedure
--can also be written as CREATE PROC sp_firstprocedure
-- To refresh your memory, comments are written after two successive hyphens
(
@id BIGINT,
@name NVARCHAR(100),
@option CHAR(1)
)
AS
BEGIN
IF(@option = ‘S’)
BEGIN
SELECT * FROM names_table
END
IF(@option= ‘D’)
BEGIN
DELETE FROM names_table
WHERE name = @name
END
END
To save the stored procedure, (which will be saved as sp_firstprocedure, which is the name of this stored procedure) either press F5 or click the red exclamatory mark in the tool bar of MS SQL. Once executed, the stored procedure will be automatically saved. Now change the CREATE word on top to ALTER (CREATE and ALTER are not case sensitive). This is because MS SQL will throw an error as a stored procedure called sp_firstprocedure already exists. So further modifications to this stored procedure will come under the ALTER operation. Hence the reason why CREATE should be changed to ALTER.
Three parameters @id, @name, @option are used. The @option is used to change different modes—in our case, the SELECT and DELETE operations. Most of the procedure is pretty self explanatory that just acts like normal SQL queries.
To check a test value in the stored procedure, you have to pass the @option ‘S’ or ‘D’ in the parameter list to choose if you have to perform the Select or Delete operation. To do this near the ALTER proc sp_firstprocedure, type 0,’’,‘S’. This means the SELECT statement is used for the procedure. Highlight the sp_firstprocedure 0,’’,‘S’ part and press F5 or the red exclamatory mark. The results will be dispayed in the results pane. Once you are done checking the stored procedure, comment the parameters.
It should look something like this
ALTER proc sp_firstprocedure –0,’’,‘S’
Note that, the parameters are used near the alter statement only to check if the stored procedure is executed properly or not.
