STORED PROCEDURES FOR NOVICE (BEGINNERS) IN SQL SERVER 2005
-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 asdbo.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_usersEND
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)
Comments
Well, the article is in reality the best on this worthw hile topic. I fit in with your conclusions and will thirstily look forward to your upcoming updates. Just saying thanks will not just be sufficient, for the tremendous clarity in your writing. I will immediately grab your rss feed to stay informed of any updates. Authentic work and much success in your business efforts!
Posted by: Sandra | December 26, 2009 09:06 AM
this article is very nice, this has a nice information, thanks you for the article.
Posted by: Victoria Baarts | February 1, 2010 02:43 PM