SQL Stored Procedures

SQL Stored Procedures

CREATE PROCEDURE DEMO_PROC
(
@EmpID INT,
@ID INT,
@RETURNVALUE INT =0 OUT
)
AS
DECLARE @COUNT INT
BEGIN
SELECT @COUNT=COUNT(*) FROM EmpTable WHERE EmpID=@EmpID AND ID=@ID
IF(@COUNT >0)
BEGIN

SET @RETURNVALUE = 1;
PRINT @RETURNVALUE
RETURN @RETURNVALUE
END
ELSE
BEGIN
SET @RETURNVALUE = 1;
PRINT @RETURNVALUE
RETURN @RETURNVALUE
END
END

Stored procedures are special objects available in sql server. Its a precompiled statements where all the preliminary parsing operations are performed and the statements are ready for execution.

Its very fast when compared to ordinary sql statements where the sql statements will undergone a sequence of steps to fetch the data

Stored procedure involves various syntax based on the parameters passed. Let me show you a generic syntax for a stored procedure.

EXEC DEMO_PROC 2,3

Stored procedure by definition is a segment of code which contains declarative or procedural SQL statements. A stored procedure is resided in the catalog of the database server so we can call it from a trigger, another stored procedure or even from client appliations.

As the definition above, the stored procedure can contains any SQL statement like INSERT, UPDATE and DELETE or any SQL data definition like CREATE TABLE, ALTER TABLE and etc. In addition, a stored procedure also supports procedure statements such as IF ELSE, WHILE... to make it as powerful as another programming languages such as C/C++, C# and Java.

Using stored procedure has several advantages :

• It is used to increases the performance of application because when we create stored procedures, they are compiled and stored in database catalog. Later when client applications call them, they are generally executed faster than uncompiled SQL statements which are sent from the client applications.
• The network traffic between application server and database server is also signification reduced because the applications don't have to send such long and uncompiled SQL statements to the server to get the data back.
• Stored procedures can be used for database security purpose because each store procedure can have its own database privileges.
• One of the most advantage of stored procedure is code reusability. Once created, a stored procedure can be reused over and over again by multiple applications.