.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Stored procedure in SQL

Posted By:Ashutosh Jha       Posted Date: July 27, 2014    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

This will explain what is stored procedure and how to write Stored procedure and what are its benefits.
 

Stored Procedures are a batch of SQL statements that can be executed in a couple of ways. stored procedures are used to make the back end logic easy. To create a stored procedure the syntax is fairly simple:

first write the keyword "create" followed by the keyword "procedure" and followed by the stored procedure name.

And then "As Begin" followed by the syntax and at the last the keyword "last". Below is the example of a simple stored procedure.

[CODE]
CREATE PROCEDURE procedurename
As
Begin
Select * from tblname
End
[/CODE]

The above is a very simple stored procedure in which we are selecting just values from table. using stored procedure we can pass the parameter also and perform any CRUD operations on tables/views.A benefit of stored procedures is that you can centralize data access logic into a single place that is then easy for DBA's to optimize.

Now suppose if you need to retrieve any particular parameter from database table using stored procedure then follow this-

Create  PROCEDURE sp_getparameter
(

@itemid INT                   --define input parameter, the one which you want to retrieve
)
AS
BEGIN
SELECT itemid+' '+itemname FROM tblname WHERE itemid=@itemtid 
END

Advantages of using stored procedures

1.Stored procedure allows modular programming.
2.Stored Procedure allows faster execution.
3.Stored Procedure can reduce network traffic.
4.Stored procedures provide better security to your data. There are much more advantage, once you will start working with this you will find !!
 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend