What is SQL Store Procedure

In this section I have covered following thing about store procedure. Things like:
• What is store procedure
• What are the usages of store procedure
• Creating simple store procedure
• Execution of store procedure
• Creating procedure with parameter
• Alter a procedure
• Deletion of store procedure

Let’s discuss each point in detail about store procedure:

1. What is Store Procedure

Sql procedure is database object that wrap with sql statements. The main idea behind store procedure is that we can write simple/complicate database logic in it, and to execute it as any time we want.

2. Usages of Store Procedure

• It Use to encapsulate our business logic into store procedure. For example instead of writing same business logic again and again in multiple places, it is good to have encapsulated business logic into a procedure and simple call by its name when situation arise.
• It prevent sql injection
• Easy to use with calling application like .NET, PHP, JAVA
• Easy to do modification a business logic/requirement in store procedure

Following is the basic syntax of store procedure

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
	-- Add the parameters for the stored procedure here
	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Let’s assume I have following business logic code to execute which populate the top 10 richest person from table tbl_person
Fig 1:

If I need to execute this query many times in database, instead of writing this code again and again, it is good to put this sql code in procedure.

3. Creating a Basic Store Procedure
I have used simple select statement for creating a store procedure.

create procedure usp_Select_top_richest
as
select top (10) id,Name, Saving from tblperson 
order by saving desc

• Creation of store procedure always has Create Procedure or Create proc statement
• usp_ : Use to declare the naming convention of store-procedure . Usp_ can be used a User store procedure. After naming convention put a sensible name of store-procedure

After creating of procedure hit the execute button so that it store in database object in compile form.

4. Execution of Store Procedure

Store procedures avoid the human effort to writing same set of query again and again. It store in database object in compile form. To execute a store procedure simply calls Execute along with procedure name.

Let look at figure 2. It is displaying top 10 richest people from table tbl_person
Fig 2

You can see the output is same as we have got output in figure 1 (the business logic query in figure 1).

5. Store Procedure with Parameter

Store procedure provides advantage to pass parameter. Parameter can be used to limit/differ the query result set.
To declare variable in procedure following rules are required.
• Variable name : variable name always begin with @ sign
• Variable data-type : variable must have data type
Let assume we need to find the name of the person in table person_table by passing id as a parameter

create proc usp_getdetialbyid
(
 @id int
)
as
select * from tblperson where id =@id

Fig 3 : following figure displaying the execution of procedure with paramter


6. Modifying existing Procedure
Store procedure is easy in modification. To modifying a procedure following syntax is used

ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

To understand in practice, let’s modify our existing Basic Store Procedure usp_Select_top_richest to select only top 5 people from table tbl_person

ALTER procedure [dbo].[sp_Select_top_richest]
as
select top (5) id,Name, Saving from tblperson 
order by saving desc

To test the output of modified procedure let execute usp_Select_top_richest
Figure 4: it now shows the top 5 rich people after modification of a procedure



7. Deleting a Procedure
Dropping a procedure is pretty much easy in database and has following syntax:

DROP PROCEDURE <stored procedure name>;

Hence we have learnt the introduction and usage of store-procedure in sql.