Creating SQL Stored Procedures

A Stored Procedure is a collection of SQL statements that is stored under one name, and executed as a single unit.
A stored procedure is a precompiled object stored in the database. This means that a procedure is compiled beforehand and is readily available for various applications to execute.
No time is spent on sending the query to the server, parsing and compiling the procedure again.

Benefits:
  • Improved performance
  • Reduction in network congestion
  • Better consistency & accuracy
  • Better security mechanism


Syntax:
CREATE PROCEDURE proc_name
[@parameter datatype [= default value] [OUTPUT]]
...
[WITH ENCRYPTION]
AS
BEGIN
SQL Statement 1
SQL Statement 2
...
[RETURN value]
END


Execute Procedure:
EXECUTE proc_name [parameter values]

Drop Procedure:
DROP PROCEDURE proc_name

Example:
CREATE PROCEDURE RegisterCustomer
@CustomerName VARCHAR(100),
@Contact VARCHAR(12),
@EMail VARCHAR(150),
@Address VARCHAR(500),
@UserID INT,
@CustomerID INT OUTPUT
AS
BEGIN
INSERT INTO Customers (CustomerName, Contact, EMail, [Address], UserID)
VALUES (@CustomerName, @Contact, @EMail, @Address, @UserID)

SELECT @CustomerID=MAX(CustomerID) FROM Customers
END

Post a Comment