In this article, I am explaining How to write stored procedures in SQL Server 2000 / 2005 / 2008.
It is good to follow practice of using Stored Procedures as they are precompiled thus reduce the overhead of compiling each time.
Stored Procedures also help in preventing SQL Injections since parameters are used in it. You can refer my article on Parameterized queries to avoid SQL Injection Parameterized Queries ADO.Net
Other benefits include
1. Create once and call it N number of times
2. Reduce traffic since instead of whole query only stored procedure name is sent from front end
3. You can give selected users right to execute a particular stored procedure.
Creating a Stored Procedure
Below figure displays the syntax for creating a stored procedure. As you can see below to create a stored procedure CREATE keyword is used.
Example
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetEmployeeDetails
@EmployeeID int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT FirstName, LastName, BirthDate, City, Country
FROM Employees WHERE EmployeeID=@EmployeeID
END
GO
Alter or Modify a Stored Procedure
Below figure displays the syntax for alter a stored procedure. As you can see below to modify a stored procedure ALTER keyword is used rest all remains the same.
Example
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE GetEmployeeDetails
@EmployeeID int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT FirstName, LastName, BirthDate, City, Country
FROM Employees WHERE EmployeeID=@EmployeeID
END
GO
Drop or Delete a Stored Procedure
Figure below displays how to drop a stored procedure. As you can see below to delete a stored procedure DROP keyword is used proceeded by the name of the stored procedure.
Example
DROP PROCEDURE GetEmployeeDetails
This completes this article. In my next article I’ll explain how to call stored procedures from front end in .net