9/29/2008

What is Stored Procedure and How to write Stored Procedure

Stored Procedure (SP, here after I will refer it as SP) is as its name indicates stored in a database . SP contains one or more then single T-SQL in it, and it is re-usable. The beauty or advantage of SP is, it will get complied and stored in Database, now when ever you required to run the T-SQL or bunch of T-SQL you don't need to write it and compile it again, just call that Stored Procedure(SP).
Whereas in case of individual T-SQL it will get complied every time you run them; so it helps to reduce your time writing T-SQL, it saves CPU and I/O times it would take to compile and to create execution plan.

There are some advantages of using SPs, they are:
1. You do not need to rewrite your T-SQL which you would required to run often
2. It will get complied and stored in DB, and this will helps you saving your CPU & I/O cycle. * In case if some modification is done in SP you would required to re-compile it.
3. Can accept parameter as input value and will return output based on it
4. You can call one SP in another
5. You can have security configured for them, i.e. can grant or deny exeecute permission for particular user/role/group
6. SP which has parameters can help you protect from SQL Injection Attack
7. SP will help you reduce your network round-trip and hence network traffic

Here are some basic instruction and input on how to write SP(s).
http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx http://www.sql-server-performance.com/articles/dev/create_stored_procedures_tsql_p1.aspx

Reactions:

1 comments:

  1. Hi Hemantgiri,

    Thanx for leting me know "What is Stored Procedure and How to write Stored Procedure" do blog frequently andwould like to read more.
    By the way do visit our new website contentxn.com, an adnetwork for bloggers. Do visit it if you would like to sign up as a blogger.

    Thanx,
    Madhav.

    ReplyDelete

I appreciate your time, thanks for posting your comment. I will review and reply to your comment as soon as I can.

Thank you
Hemantgiri