Introduction of the Cursors in SQL Server

No.of Views2179
Bookmarked0 times
Downloads 
Votes0
By  youngmurukan   On  20 Jul 2010 10:07:24
Tag : Sql Servers , Stored Procedures
Stored procedures are stored in SQL Server databases. The simplest implication of stored procedures is to save complicated queries to the database and call them by name, so that users won’t have to enter
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

Introduction

Stored procedures are stored in SQL Server databases. The simplest implication of stored procedures is to save complicated queries to the database and call them by name, so that users won’t have to enter,the SQL statements more once. As you see, stored procedures have many more applications, and you can even use them to build business rules into the database.
How to create a Stored Procedure.


As shown given below, created a Stored Procedure for Inserting records into Table call Holiday_Details, which has Code and Description fields.In this Stored Procedure, passing two parametrs as INPUT Parameters and one OUTPUT parameter.

Normally in Stored Procedure we can pass parameters as Input / Output Stored parameters. When you define output parameters, we have to implicitly specify the OUTPUT Keyword.
Here I have shown the simple stored procedure.

CREATE PROCEDURE[dbo].[SP_Holiday]@Code char(3),@Desc varchar(100),@flag bit,@ErrVarchar(MAX)=Null OUTPUTASBegin Transactionif @flag=0beginINSERT INTO Holiday_Details Values(@code,@Desc)endif @flag=1beginUPDATE Holiday_Details SET Description=@Desc WHERE Code=@codeendIf @@ERROR <>0BeginSet @Err=cast(@@Error as varchar(max))Rollback Transactionprint @ErrreturnEndElse if @@ERROR<>0BeginSet @Err='Successfully done!!'print @ErrEndCommit Transaction

 Here we are trying Insert/Update the records into Holiday_Details. For diferentiate the Insert and Update, we are using @flag input parameter. So when we execute this parameter we should specify the @flag whether it is Insert/Update.@Err parametr is Output parameter for get the status of execution whether it is successfully Inserted/Updated or thrown any error.
So, if you execute this procedure, you would get the output of status.Execute of a Stored Procedure When you execute a Stored Procedure, you have to use either Execute/EXEC
(Exec is special case, I will explain about in another article) keyword for execute the Stored Procedure.

EXEC SP_Holiday 'bb1','ffff',0

 

EXECUTE SP_Holiday '332','ffff',0

Here we have not pased value for OUTPUT parameter since we have assined Null as intial value which means in case if you forget topass the paraneter, Variable would take the Intialized value.So, once you run this stored Procedure, this would return the value of @Err parameter. Main purposes of Using Stored Procedures


When as SQL statement, especially a complicated one, is stored in the database as stored procedure, its execution plan is designed once, cached, and is ready to be used again.
Moreover, stored procedures can be designed once, tested, and used by many usres and applications. If the same stored procedure is used by more than user, the DBMS keeps only one copy of the procedure in memory, and all users share the same instance of the procedure. This means more efficient memory utilization.


Finally, you can limit user access to database’s tables and force users to access the database throgh stored procedures. This is simple method of enforcing business rules.Happy coding.

 

 
Sign Up to vote for this article
 
About Author
 
youngmurukan
Occupation-Not Provided
Company-Not Provided
Member Type-Senior
Location-Not Provided
Joined date-12 May 2009
Home Page-Not Provided
Blog Page-Not Provided
 
 
Other popularSectionarticles
Comments
There is no comments for this articles.
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top
</