Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

what is stored procedure in Sql server | what are the advantages of using stored procedures in sql server

Jul 28, 2011
Introduction:

Here I will explain about what is stored procedure is and advantages and disadvantages of stored procedures in sql server

Description:

A stored procedure is a group of sql statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure

Sample of creating Stored Procedure

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE dbo.sp_who
AS
    SELECT FirstName, LastName FROM Person.Person;
GO
EXEC sp_who;
EXEC dbo.sp_who;
GO
DROP PROCEDURE dbo.sp_who;
GO

Advantages of using stored procedures

a)    a) Stored procedure allows modular programming. 

You can create the procedure once, store it in the database, and call it any number of times in your program. 

b)    b) Stored Procedure allows faster execution. 

If the operation requires a large amount of SQL code is performed repetitively, stored procedures can be faster. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times. 

c)     c) Stored Procedure can reduce network traffic. 

An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.

d)    d) Stored procedures provide better security to your data

Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.

In SQL we are having different types of stored procedures are there

a)    System Stored Procedures
b)    User Defined Stored procedures
c)    Extended Stored Procedures

System Stored Procedures:

System stored procedures are stored in the master database and these are starts with a sp_ prefix. These procedures can be used to perform variety of tasks to support sql server functions for external application calls in the system tables 

Ex: sp_helptext [StoredProcedure_Name]

User Defined Stored Procedures:

User Defined stored procedures are usually stored in a user database and are typically designed to complete the tasks in the user database. While coding these procedures don’t use sp_ prefix because if we use the sp_ prefix first it will check master database then it comes to user defined database

Extended Stored Procedures:

Extended stored procedures are the procedures that call functions from DLL files. Now a day’s extended stored procedures are depreciated for that reason it would be better to avoid using of Extended Stored procedures. 

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

46 comments :

raja said...

Great work suresh...keep serving.

Suresh Dasari said...

Thanks Rajesh keep visiting

Anonymous said...

pls update this website for every month...

karunasri said...

hi great work

Anonymous said...

hi suresh...i daily read your all articles..
all are very usefull..thanks for this.


and i am reqesting you sir if possible publish articles for cursors with practical example

Anonymous said...

hi suresh iam new one to your site i felt very good while visiting ur site.code sample provided by u is awesome.i expect more article in wcf.could you post it please.iam at beginning level in wcf.....

Tapan said...

hi Suresh Dasari.....
Please tell me How to add Facebook Like button in a Asp.net web page

renganathan said...

super work keep it up....
thanks for this work

Chandru A said...

Dear suresh,

i need to know more about stored procedure...

pls post for me the program with demo .

harshal said...

Nice post, i really i like it

Unknown said...

nice post thanku

Anonymous said...

really nice site i m new to .net your site only is godfather for me so much thanks

Unknown said...

Very Good, All Articles are niCE...

kishan's said...

very nice suresh

Unknown said...

very nice

Unknown said...

Thank u for sharing your knowledge.

Anonymous said...

nice and clear explain--by bala

Anonymous said...

I have Clarified my doubts in oops

Anonymous said...

great work....

Anonymous said...

nicely explained thank you suresh

Unknown said...

gd information sharing

satya said...

hi suresh this is satya and i visited ur web site it is very good to understand for begineers.I am learning .net and i am trying for job ur site is very helpful to my preparation

Unknown said...

Thank you suresh,i satified yopur comment store procedure.

Hamid said...

Thank you Suresh.
It is very helpful for me.
and I hope it wil be helpful fro every software developer.

Please keep on it.

Thnks
Hamid

Anonymous said...

thanx man i liked it

Unknown said...

HELLO Admin..This is Muruganantham.i need a stored procedure sample programs using sql server from A to Z sample programs..plz send me at muruganantham.msc@gmail.com..

Unknown said...

hi suresh dialy i am reading your articles...i want one small task like this

new user sign up in hospital projects using stored procedures in gridview...Could u plz share this article also .it is more helpful for me......

abcfg bgth said...


Hi suresh,

i need to know more about stored procedure...

plz send some practical programs to me at snraju625@gmail.com

very useful for job preparation.
thank you very much suresh

Lokesh said...

Thanks ..

Unknown said...

thank u suresh u r posts r really usefull a lot......

Unknown said...

hi, if u great u can send c# related documents to my mail id
kumar.msbi12@gmail.com.

Unknown said...

Hi, I want learn c#. please give some suggestions

Anonymous said...

Very useful blog .... :)

csmidhu said...

Nice one !! but please list disadvantage for the same.

Anonymous said...

eterttert

Unknown said...

You are like Teacher to us who is providing a wonderful knowledge to us.

Unknown said...

Very good experience visiting your site. I am a java guy and got a project to work in C#.net. Your valuable input really works for me and now i m working on c#.net efficiently by taking input from your site regularly. Thanks Suresh for your input for a guy like me who have different platform experience and working on dotnet by getting your inputs.

Rajaah said...

Thnks for the info. Good work. Simple and clear. Keep going...

Unknown said...

Hi suresh,

i need to know more about stored procedure...

plz send some practical programs to me at rsajidur65@gmail.com

very useful for job preparation.
thank you very much suresh

Anonymous said...

thank u suresh

Unknown said...

Thank you .
It is very helpful for me.

Unknown said...

Nice Suresh.
It is very helpful for me.

Unknown said...

thanks,
its very helpful for meeeeeeeeeeee

Unknown said...

hi
i had a doubt in step a. Please explain it.

Unknown said...

I did not get what is stored procedure...and why we use it..and what will be happned if we don't use it..please help me out with easy examples..thanks

Anonymous said...

Great article... It is very cleared answers and easy to understand..
Thank youuuu

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.