As the saying goes,"Necessity is the mother of invention.
It is true, but its father is creativity, and knowledge is the midwife"; Well guys I am very happy to share my knowledge too regarding stored Procedures.
For now, I have decided to write a preliminary description on Stored Procedures 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 procedure.
Furthermore 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 : 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.
OpenSql Server 2005 2.
Right click -> Create Database 3.
Enter new Database name ( dsp_users ) 4.
Now Expanddsp_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 uidint 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 ur own procedure.
It starts with the keyword CREATE PROCEDURE written as follows: CREATE PROCEDUREusp_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 procedure name is usp_display.
usp defines "User Stored Procedure".
The above procedure displays all the records of the emp_users table.
Now in the menu-tab up (Click Query-> Execute) .
Now it says"Command completed successfully".
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 commandexec usp_displayand run it.
The records of the table emp_users gets displayed.
It is true, but its father is creativity, and knowledge is the midwife"; Well guys I am very happy to share my knowledge too regarding stored Procedures.
For now, I have decided to write a preliminary description on Stored Procedures 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 procedure.
Furthermore 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 : 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.
OpenSql Server 2005 2.
Right click -> Create Database 3.
Enter new Database name ( dsp_users ) 4.
Now Expanddsp_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 uidint 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 ur own procedure.
It starts with the keyword CREATE PROCEDURE written as follows: CREATE PROCEDUREusp_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 procedure name is usp_display.
usp defines "User Stored Procedure".
The above procedure displays all the records of the emp_users table.
Now in the menu-tab up (Click Query-> Execute) .
Now it says"Command completed successfully".
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 commandexec usp_displayand run it.
The records of the table emp_users gets displayed.
Source...