04 December, 2012

Stored Procedures in SQL Server.

A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. SQL Stored procedures return data in four ways:
  • Output parameters, which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time).
  • Return codes, which are always an integer value.
  • A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.
  • A global cursor that can be referenced outside the stored procedure
Why to use stored procedures in SQL server:
  • Execution plan retention and reuse
  • Query auto-parameterization
  • Encapsulation of business rules and policies
  • Application modularization
  • Sharing of application logic between applications
  • Access to database objects that is both secure and uniform
  • Consistent, safe data modification
  • Network bandwidth conservation
  • Support for automatic execution at system start-up
  • Enhanced hardware and software capabilities
  • Improved security
  • Reduced development cost and increased reliability
  • Centralized security, administration, and maintenance for common routines
How to create Stored Procedure in SQL:
1.   In Object Explorer, connect to an instance of SQL Server Database Engine and then expand that instance.
2.   Expand Databases, expand the database in which the stored procedure belongs, and then expand Programmability.
3.   Right-click Stored Procedures and then click New Stored Procedure.
4.   On the Query menu, click Specify Values for Template Parameters.
5.   In the Specify Values for Template Parameters dialog box, the Value column contains suggested values for the parameters. Accept the values or replace them with new values, and then click OK.
6.   In the query editor, replace the SELECT statement with the statements for your procedure.
7.   To test the syntax, on the Query menu, click Parse.
8.   To create the stored procedure, on the Query menu, click Execute.
9.   To save the script, on the File menu, click Save. Accept the file name or replace it with a new name, and then click Save.
Example:
CREATE PROCEDURE spVendorByState
          @VendorState
varchar(50)AS
BEGIN

          -- SET NOCOUNT ON added to prevent extra result sets from
          -- interfering with SELECT statements.
          SET NOCOUNT ON;

          -- Insert statements for procedure here
          SELECT VendorId,VendorFName,VendorLName,VendorCity,VendorState,VendorCountry,PostedDate,
          VendorDescription
          FROM Vendor Where VendorState = @VendorState ORDER BY PostedDate
There are some different concepts of stored procedures.
  • A stored procedure is one or more SQL statements that have been compiled and stored with database. A stored procedure can be started by application code on the client.
  • Stored procedure can improve database performance because the SQL statements in each procedure are only compiled and optimized the first time they are executed. In contrast SQL statements that are sent from a client to the server have to be compiled and optimized every time there are executed.
  • In addition to SELECT statement, a stored procedure can contain other SQL statements such as INSERT, UPDATE, and DELETE. It also contains control-of-flow language.
  • A trigger is a special type of procedure that executes when rows are inserted, updated or deleted from table.
  • A user defined function (UDF) is a special type of procedure that can return a value or a table. 

15 comments:

  1. This post is invaluable. When can I find out more?


    Feel free to visit my web blog :: relaxing massage singapore

    ReplyDelete
  2. Pretty component to content. I simply stumbled upon your web site and in accession capital to say that
    I acquire actually enjoyed account your weblog posts.
    Any way I'll be subscribing on your augment and even
    I achievement you access constantly rapidly.

    Have a look at my website :: massage in first trimester

    ReplyDelete
  3. whoah this blog is fantastic i really like studying your posts.

    Stay up the good work! You understand, a lot of individuals are searching around for this information, you can help them greatly.


    Look into my web site massage advantage

    ReplyDelete
  4. excellent issues altogether, you just gained a brand new reader.
    What might you suggest about your put up that you simply made a few
    days in the past? Any sure?

    My blog post ... relaxing massage ealing; 4shared.com,

    ReplyDelete
  5. Hello there! I could have sworn I've visited this website before but after
    browsing through a few of the articles I realized it's new
    to me. Anyhow, I'm certainly pleased I came across it and I'll be book-marking it and checking back frequently!


    Have a look at my website - relaxing massage pics

    ReplyDelete
  6. Hi there, yup this article is genuinely fastidious and I have learned lot of things from it concerning blogging.

    thanks.

    Here is my blog post; relaxing massage quotes ()

    ReplyDelete
  7. Pretty! This was an extremely wonderful article.
    Many thanks for providing this info.

    my blog post: wild growth hair oil

    ReplyDelete
  8. Hello everyone, it's my first visit at this web page,
    and post is genuinely fruitful designed for me, keep up posting
    these posts.

    my homepage :: beauty of fashion

    ReplyDelete
  9. Hey very interesting blog!

    My weblog :: beauty wellness spa

    ReplyDelete
  10. You actually make it seem so easy with your
    presentation but I find this matter to be really something that I think I would never understand.

    It seems too complex and extremely broad for me. I'm looking forward for
    your next post, I will try to get the hang of it!

    My blog post; massage tips pinterest

    ReplyDelete
  11. Appreciation to my father who told me concerning this weblog, this weblog is actually amazing.


    my web page massage york pa

    ReplyDelete
  12. Excellent post. I was checking constantly this blog and I am impressed!
    Very helpful information specially the last part :) I care for such information a lot.
    I was looking for this particular information for a very long time.
    Thank you and good luck.

    My web-site permanant hair removal

    ReplyDelete
  13. Quality posts is the important to be a focus for the
    viewers to go to see the web site, that's what this site is providing.



    Take a look at my site :: day spa pedicure

    ReplyDelete
  14. I think that is one of the such a lot important info for me.

    And i'm glad reading your article. However want to
    observation on some basic issues, The website style is great,
    the articles is truly great : D. Excellent process, cheers

    Feel free to visit my homepage ... Laser Hair Removal Or Waxing (2Minutes2Book2.Wordpress.Com)

    ReplyDelete
  15. Thanks for sharing such a valuable information.Im very thankful to you that you had given me this opportunity to write on this blog
    All movement organizations of Packers And Movers Pune
    recorded with us have their own particular vehicle for transportation of your profitable products. This makes it considerably snappier, incite and bother free.
    http://thebusinessplace.in/packers-and-movers-pune-to-patna

    ReplyDelete

Your feedback is always appreciated. I will try to reply to your queries as soon as possible- Amol Ghuge

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