Disadvantages and Benefits by Using Stored Procedure

Stored procedure is a subroutine which is used for the accessing a relational database management system. It is also used for the storing the batch of statements in SQL server. Stored procedures are also known as StoPro, proc, SP, sproc or StoredProc and actually stored in the database data dictionary. Stored Procedure can returns or accepts value if needed. There are two way by whihch we can store a batch of statements in SQL Database, one is function and another is stored procedure.


Differences between function and stored procedure are given below:

Function must return a value but it is not necesary in the case of stored procedure.
Function has IN parameter only while stored procedure has OUT or IN Parameter.
Stored procedure can return no value or return multiple value by using OUT parameter.
Function could used SELECT Parameter while stored procedure cann't used SELECT Parameter.

Syntax for stored procedure is given below:

CREATE PROC[EDURE] urProcedureName
[<@InputParameter> Datatype default] [, 1..n] ]
[<@OutputParameter> Datatype OUT[PUT]][, 1..n] ]
[WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
AS
[BEGIN ]
[SqlStatements;][1..n]
[END]
GO


Benefits & disadvantages of stored procedure: Some of the benefits and disadvantages of stored procedure are given below.

Benefits:
Reduced network usage between client and server
Improved security
Enhanced hardware and software capablities
Reduced development cost and increased reliability
Centralized security, administration, and maintenance for common routines

Disadvantages: Stored procedure is vendor specefic language, if you want to change vendor's database then you have to rewrite your stored procedure language.