-Tauseef. M
As the saying goes, “Necessity is the mother of invention. It is true, but its father is creativity, and knowledge is the midwife"; Well guy's I am very happy to share my knowledge too regarding stored Procedures. For now, I have decided to write a preliminary description on Stored Procedure in SQL Server 2005.
Starting with what are stored Procedures?
In simple terms "Stored Procedures are a set of already written SQL statements that are saved in the database." If you are executing the same query over and over again, then it would make sense to simply put it into stored procedure.
Furthermore, with a stored procedure, you can store all the logic in the database, and use a simple command to call the stored procedure. Later, if you decide to migrate from ASP to java, J2EE, you only need to change the application layer as much of the business logic will remain in the database.
Getting Started with Stored Procedures
Basic requirements to begin writing stored procedures:
1. A database Management System (Ex: Sql Server 2005)
2. A database built inside the database Management System.(Ex : Demo database)
3. A Query Analyzer which is Built-in into Sql server 2005. You need not worry!!
Writing Your First Stored Procedure
So let's begin our countdown, creativity all that matters!!
1. Open Sql Server 2005
2. Right click -> Create Database
3. Enter new Database name (dsp_users)
4. Now Expand
dsp_users, right-click on tables and create a new table with table name as
dbo.emp_users with the column names as below:
- uname nvarchar(50)
- upass nvarchar(50)
- uaddress nvarchar(50)
- uage int
- uid int
5 Now expand Programmability -> Right click Stored Procedures and select New Stored Procedure.
Now, here the top section is useful for comments about the stored procedure, a change log, and other pertinent information. While this is not required, it is just a good programming habit
============================================================
-- Author: Tauseef
-- Create date: 09/03/09
-- Description: Stored procedure to display results
=========================================================
Now just below this, you will create a store procedure. It starts with the keyword CREATE PROCEDURE written as follows:
CREATE PROCEDURE usp_display
/*
We will put the variables in here, if there are any
*/
AS
/*
This is where the actual SQL statements will be written as below
*/
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT *from emp_users
END
Here now the stored procedure name is usp_display. usp defines "User Stored Procedure". The above stored procedure displays all the records of the emp_users table.
Now in the menu-tab up (Click Query-> Execute) . Now it says “Command completed succesfully" . Thus you have created your first stored procedure.
How to execute the created stored procedure?
Its simple, just open a new query and type the following command exec usp_display and run it. The records of the table emp_users gets displayed.
More Sophisticated Stored Procedures using input and output variables will be explained later.
(The author Tauseef. M, is a Software Developer at Binary Spectrum)