In this tutorial of SQL Server, We will learn Stored Procedure in SQL Server. Stored Procedure is a set of Prepared SQL Statements that can be reused multiple times.

After completing this tutorial you will be able to understand:

  • What is Stored Procedure IN SQL Server?
  • Advantages of SQL Stored Procedure.
  • How to create Stored Procedure in SQL Server?

Introduction:

Stored Procedure is a pre compiled SQL Code that is saved so that we reuse the code over and over again. Stored Procedure is a group of SQL Statements that are used to perform a specific task.

In addition writing the same code over and over again, we can also pass parameters to the Stored Procedure to get the required output.

The main benefit of using Stored Procedure is it increases your Database Performance.

Stored Procedure

The syntax for Creating Stored Procedure:

Advantages of using the Stored Procedure:

1- Increase Performance:

The Stored Procedure increases the performance by reducing the amount of data sent to the database server. Stored Procedure becomes more useful when we have a server with less bandwidth.

2- Recompilation is not required:

The normal SQL Statement is compiled every time when it is sent to the server for execution whereas Stored Procedure compiled only once when it is created. It does not require recompilation before execution until it is modified.

3-Provides Code ReUSability:

As we have already discussed with Stored Procedure we don’t need to write a SQL Code over again and again. We create a Stored Procedure once and it can be used by multiple clients on the server for multiple times.

4- Store Business Logic:

We can use a Stored Procedure to store Business Logic of the component. It is secure to Store Business Logic in a Stored Procedure. If we need to change our business logic then we may only need to change the logic inside the Stored Procedure not in the files contained on the Web Server.

Example OF Stored Procedure in SQL Server:

Create the following Database and Table.

CREATE DATABASE SPDemo
USE SPDemo

Stored Procedure for Inserting Record in Employee Table:

Testing uspAddEmployee:

Stored Procedure for Reading Records in Employee Table:

Testing uspGetEmployeeRecords:

Stored Procedure of Updating Records in Employee Table:

Testing uspUpdateEmployee:

Stored Procedure of Deleting Records in Employee Table:

Testing uspUpdateEmployee:

You can download the script for this tutorial from here.

Conclusion:

Hope you understand this important tutorial of SQL Server. I would like to have your Suggestions, Feedback and comments.

View More:

Thank You.

30 Comments

  • F*ckin’ amazing issues here. I am very satisfied to see your article. Thanks a lot and i am looking forward to touch you. Will you please drop me a mail?

  • Youre so cool! I dont suppose Ive learn something like this before. So good to search out anyone with some unique ideas on this subject. realy thanks for starting this up. this website is something that’s wanted on the internet, somebody with a bit of originality. helpful job for bringing one thing new to the web!

  • What i don’t understood is in reality how you are not really much more smartly-favored than you may be now. You’re so intelligent. You know thus significantly in the case of this subject, produced me personally imagine it from a lot of various angles. Its like women and men are not involved unless it is something to accomplish with Lady gaga! Your personal stuffs excellent. At all times care for it up!

  • Howdy! I know this is kinda off topic nevertheless I’d figured I’d ask. Would you be interested in trading links or maybe guest writing a blog post or vice-versa? My website covers a lot of the same topics as yours and I think we could greatly benefit from each other. If you are interested feel free to send me an e-mail. I look forward to hearing from you! Great blog by the way!

  • Thanks a lot for sharing this with all of us you actually know what you are talking about! Bookmarked. Kindly also visit my site =). We could have a link exchange arrangement between us!

  • certainly like your web-site but you need to check the spelling on several of your posts. Several of them are rife with spelling issues and I find it very troublesome to tell the truth nevertheless I’ll definitely come back again.

  • I have been browsing on-line more than three hours nowadays, but I by no means found any fascinating article like yours. It’s beautiful value enough for me. Personally, if all website owners and bloggers made good content as you probably did, the internet will be much more helpful than ever before.

  • Good day! This is my first visit to your blog! We are a group of volunteers and starting a new project in a community in the same niche. Your blog provided us valuable information to work on. You have done a outstanding job!

  • Hi there! I just wanted to ask if you ever have any trouble with hackers? My last blog (wordpress) was hacked and I ended up losing months of hard work due to no data backup. Do you have any solutions to protect against hackers?

  • Great beat ! I wish to apprentice while you amend your website, how could i subscribe for a blog website? The account aided me a acceptable deal. I had been a little bit acquainted of this your broadcast provided bright clear idea

  • Most of what you articulate happens to be supprisingly appropriate and it makes me wonder why I hadn’t looked at this in this light before. This particular piece truly did switch the light on for me personally as far as this specific issue goes. Nonetheless there is one issue I am not really too comfy with and while I attempt to reconcile that with the actual central theme of the issue, allow me observe just what all the rest of the subscribers have to point out.Very well done.

  • Definitely believe that that you stated. Your favorite reason appeared to be at the internet the simplest factor to be aware of. I say to you, I certainly get irked while folks consider concerns that they plainly don’t know about. You controlled to hit the nail upon the highest and defined out the entire thing without having side effect , folks could take a signal. Will likely be back to get more. Thanks

  • Thanks for another magnificent post. The place else may just anybody get that kind of information in such an ideal approach of writing? I have a presentation subsequent week, and I’m at the search for such information.

  • I like the helpful info you provide in your articles. I will bookmark your weblog and check again here frequently. I am quite sure I’ll learn lots of new stuff right here! Good luck for the next!

  • I’m no longer certain the place you are getting your information, however great topic. I needs to spend some time studying much more or working out more. Thanks for great info I was in search of this information for my mission.

  • 6033 154645Some genuinely rattling work on behalf of the owner of this web site , utterly fantastic content material . 703432

  • An impressive share, I just given this onto a colleague who was doing a little evaluation on this. And he in truth purchased me breakfast as a result of I discovered it for him.. smile. So let me reword that: Thnx for the treat! But yeah Thnkx for spending the time to discuss this, I really feel strongly about it and love studying more on this topic. If doable, as you grow to be experience, would you mind updating your blog with extra details? It’s extremely useful for me. Big thumb up for this blog submit!

  • 922676 849416Yay google is my king helped me to discover this outstanding site! . 461657