Click here to Skip to main content
14,265,813 members

Learn about Stored Procedures

Rate this:
5.00 (4 votes)
Please Sign up or sign in to vote.
5.00 (4 votes)
14 Aug 2019MIT
In this blog post, you will learn about stored procedures

What is a Stored Procedure?

A stored procedure is a group of one or more database statements stored in the database’s data dictionary and called from either a remote program, another stored procedure, or the command line. Stored procedures are commonly called SPROCS, or SPs. Stored procedure features and command syntax are specific to the database engine. Traditionally, Oracle uses PL/SQL as its language; whereas, SQL Server uses T/SQL.

Main Parts of a Stored Procedure

Stored procedures can be thought of having three main parts:

Inputs

Stored procedure can accept parameter values as inputs. Depending on how the parameters are defined, modified values can be passed back to the calling program.

Execution

Stored procedures can execute SQL statements, utilize conditional logic such as IF THEN or CASE statements and lopping constructs to perform tasks.

A stored procedure is able to call another stored procedure.

Stored procedure can become very handy as they can manipulate results of SQL queries via cursors. Cursors allow the procedure to access results row by row. In essence, you can use cursors to loop through a SQL statement’s result. This can slow down database performance, so be intelligent about your use of cursors!

Outputs

A stored procedure can return a single value such as a number or text value or a result set (set of rows). Also, as mentioned, depending on how the inputs are defined, changed values to inputs can be propagated back to the calling procedure.

Example Stored Procedure

Here is an example of a stored procedure that takes a parameter, executes a query and return a result. Specifically, the stored procedure accepts the BusinessEntityID as a parameter and uses this to match the primary key of the HumanResources.Employee table to return the requested employee.

Stored Procedure Parts

Though this example returns a single row, due to the fact that we are matching to the primary key, stored procedures can also be used to return multiple rows, or a single (scalar) value.

Learn more in this introductory article!

Stored procedures can be called from within SQL server. To call this stored procedure from the SQL server command line or from another stored procedure, you would use the following:

exec HumanResources.uspFindEmployee 3

Stored procedures can also be called from within a programming language. Each language, such as PHP or C#, has its specific methods for doing so.

Benefits of Stored Procedures

There are several benefits to using stored procedure. Though it doesn’t make sense to encapsulate every query you write within a stored procedure, there are some good reasons to use them. Here are some benefits frequently mentioned.

Network Efficiency

Stored procedures can include many commands and process large amounts of information to obtain the desired result. By keeping all the programming logic on the server, we can avoid having to pull query results across the network to be processed by a client program.

Encapsulate Business Logic

Databases can handle many clients and calling programs. But it isn’t uncommon for a database to handle requests from a custom program, such as C#, and a built in program such as Excel. In order to ensure key business logic remains consistent, it makes sense to run the business logic on the server in stored procedures rather than rely on each version of a program to implement and properly execute the rules.

This helps with consistency, as programs execute the same logic. This also means that the quality of the data is better.

Maintainable

When complicated business rules and programming logic are centralized into stored procedures, it makes making changes much easier. Rather than having to hunt down areas in each application and make changes, you only need to make changes to the stored procedure.

Once saved and compiled all calling program benefit from the change. Again, this can help increase the quality of your database.

Stronger Security

You can set up your database security so that applications can only access and modify data through stored procedure calls. Ad-hoc queries or direct access to tables would not be allowed.

Security access can also be delegated. In effect, the stored procedure code is executed with higher access credentials than the caller. This means you don’t have to give every user that need to call your stored procedures all the access. In SQL Server for instance, you can use the EXECUTE AS clause with creating a stored procedure to impersonate another user.

Using stored procedure also helps prevent script injection attacks. Any input parameters are treated as literal values and not executable code. This make it harder for attackers to try and trick your queries into performing unintended actions.

Disadvantages

There are some disadvantages to using stored procedures and you’ll find some blog posts encouraging you to not use them. Some of the disadvantages I think are worth mentioning include:

Portability

Stored procedures are written in vendor specific languages and that makes it hard to transfer them from one installation, such as Oracle, to another like SQL Server.

Testing

Testing and debugging stored procedures can be tricky. It can be more difficult to put together the debugging tools to allow you to step through and trace stored procedures. It definitely has gotten better than in the early 2000s, but still not as easy as it is to debug native code.

Version Control

It is harder to keep track of changes made to stored procedures than it is native source code. Unlike most native code IDEs, there aren’t many ways to integrate source code control into the stored procedure development tool. Because of this, most version control activities are limited to extracting the stored procedure as a CREATE PROCEDURE script and then manually importing into a version control system.

My Personal Experience

I feel that stored procedures have their place. I’ve put together some extensive applications and have used stored procedures for all the CRUD operations. I think that using stored procedures to insert and modify data is a really good idea.

I’ve seen that the systems I wrote that extensively used stored procedure passed security tests and were less prone to raise vulnerability testing issues.

However, even in these applications, I found that you can’t solve all your problems stored procedures! We had put together some extensive ad-hoc report writers and needed a way to generate SQL code on the client and present it to the server. For these ad-hoc cases, I shy away from stored procedures.

Remember! I want to remind you all that if you have other questions you want answered, then post a comment or tweet me. I’m here to help you.

The post Learn about Stored Procedures appeared first on Essential SQL.

License

This article, along with any associated source code and files, is licensed under The MIT License

Share

About the Author

essentialSQL
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here ==> http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
GeneralA few comments Pin
MadMyche14-Aug-19 13:05
mveMadMyche14-Aug-19 13:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Technical Blog
Posted 14 Aug 2019

Stats

1.4K views
2 bookmarked