A stored procedure executes one or more SQL statements. In order to use a stored procedure:
- you create a stored procedure containing the SQL statement(s). The DBMS will store it (them) in the database.
- When a program needs to execute the SQL statement(s), it calls the stored procedure. The DBMS executes the corresponding actions in the database.
The diagram below illustrates these two steps:
This article explains techniques commonly used in a professional environment:
- creating a simple stored procedure.
- stored procedure with input parameter validation.
- stored procedure with output parameters.
- granting rights to users to execute a stored procedure, deleting a stored procedure.
This article also lists the pros and cons of using a stored procedure.
Executing a simple SQL statement using a stored procedure:
We want to execute the following statement using a stored procedure:
Stored procedure creation:
Use the following syntax to create the corresponding stored procedure:
The diagram below shows on the left hand side the SQL statement we want to replace, and on the right hand side the stored procedure call and the stored procedure creation. The SQL statement is colored in red, the stored procedure name in green and the input parameter in blue:
In this example:
- searchClientByState is the name of the stored procedure.
- @state is an input parameter of type varchar(30).
- select first_name, last_name, phone_number from clients where state=@state is the statement to execute. In this statement, the parameter @state takes the value given in input when calling the store procedure. Example: if the input parameter @state takes the value 'Karnataka', then the statement executed will be "select first_name, last_name, phone_number from clients where state='Karnataka'".
Stored Procedure Execution:
To execute the stored procedure created above:
searchClientByState is the name of the stored procedure, and ‘Karnataka’ is the value taken by the input parameter.
Granting a stored procedure to a specific user
A stored procedure is a database object like a table is. As a result:
- you need to be a database administrator to create a stored procedure.
- you need to grant to users the right to execute this stored procedure.
- you can drop a stored procedure.
To allow users to execute this stored procedure, use the grant keyword. In the following example, the procedure is called searchClientByState. We allow a user called arjunj to execute the stored procedure:
Dropping a stored procedure:
To drop the stored procedure, use the keywords drop proc. Example: to drop the stored procedure called searchClientByState :
Raising a specific error if a condition fulfilled:
We want the stored procedure to show a specific error message when something wrong happens. In our example, we will raise a different error in each of these scenario:
- empty input parameter provided.
- numeric parameter provided instead of text (state should not be numeric).
- no record found (in that case, we indicate which parameter was given in input).
In this example:
- if @state='' begin … end : this means that if the parameter @state is empty, we execute what is between the keywords “begin” and “end”.
- raiserror : will cause the stored procedure to raise the error indicated in parenthesis.
- return : stops the stored procedure execution. What is after this keyword will not be executed.
- raiserror ('no record found for state:%s',12,1,@state): in the message “no record found for state:%s”, %s is a string which will take the value of parameter @state.
12 is a number to indicate the severity of the error. Use any number between 11 and 16 to indicate an error (case where you stop the execution of the stored procedure). You can use a number below 11 to indicate a warning. This number is going to appear in the error message when the DBMS raises the error.
1 is a number that helps the user find out where the error is coming from in the code. To achieve this result, put a different number (must be between 0 and 255) in each place in the code where you placed a raiserror.
- @@ROWCOUNT : gives the number of rows returned (in the case of a select) or executed (in the case of an update or delete) by the statement executed just before.
Execution and results:
exec searchClientByState ''
Msg 50000, Level 12, State 1, Procedure searchClientByState, Line 79
Empty state provided
State not returning any result:
exec searchClientByState 'Kartaka'
Msg 50000, Level 12, State 2, Procedure searchClientByState, Line 87
no record for state:Karnka
exec searchClientByState 123
Msg 50000, Level 12, State 3, Procedure searchClientByState, Line 88
State cannot be numeric
Stored procedure giving a parameter as output:
We want to put values returned by a stored procedure in parameters @first_name, @last_name and @phone_number. The stored procedure name is searchClientById. It takes as input parameter the id of the client for which we want to retrieve data.
In this example:
- The keyword output means the parameter on the left of this keyword (example: @first_name) is an output parameter.
- select @first_name=first_name means that we assign the value of column first_name to the parameter @first_name.
Execution (from an SQL script):
- declare @first_name varchar(30) means we declare a variable @first_name of type varchar(30).
- select @id=1 assigns the value 1 to parameter @id
- exec searchClientById @id,@first_name output,@last_name output,@phone_number output executes the stored procedure which name is searchClientById. It takes the value of @id as an input parameter and will populate the value of parameters @first_name, @last_name, @phone_number with the value returned by the stored procedure.
- select @first_name,@last_name,@phone_number means that you display the value of these 3 parameters on the screen when executing your script.
Tips when modifying stored procedures:
Dropping the stored procedure if it already exists:
In a professional environment:
- the stored procedure will be kept in a file.
- developers can modify an existing stored procedure. This involves deleting the procedure and re-create it again.
As a result, in the file containing the code to create the procedure, we add in the beginning the following code. This code drops (deletes) the stored procedure if it already exists:
if OBJECT_ID('searchClientByState','P') IS NOT NULL means “if a procedure called searchClientByState already exists in the database”.
Just after this, we place the code creating the stored procedure. At each project, we execute such a file, which will:
- delete the old version of the stored procedure (if there is an older version existing in the database).
- create the new version of the stored procedure.
Grant statement in a separate file:
We keep the grant statements in another file. The grant statements give users the right to use the store procedure. In case we have many users, we grant the right to execute the stored procedure to a role. Then, we assign the role to several database users.
This is how we do it:
First we create a login to administer the role:
Secondly, we create the role and assign someone to administer this role:
We assign this role to existing users:
Finally, we grant to the role the right to execute the stored procedure:
As a result, all users having the role “viewers” will be able to execute the stored procedure.
Advantages and disadvantages of using stored procedures:
Using a stored procedure can have the following advantages:
- No back and forth between your program and the database: you can execute several statements inside a procedure and add logic between the statements. If you would do the same without a stored procedure, you would have for each statement to send the full SQL statement to the DBMS through the network and then get back the response. With the stored procedure, you just send the call to the stored procedure, the statements are executed, and then you get the answers from the stored procedure.
- Speed gain (not guaranteed): in some cases, using a stored procedure can be faster: the interpretation of the statement is done during database creation, so it does not need to be done during the execution. This advantage is cancelled if you use logic to execute different statements depending on the input parameter. Also some databases are keeping in memory the interpretation of statements previously executed. In that case, it is not bringing a gain in speed to have the interpretation stored in the database during the stored procedure creation.
- Security (more precise access restriction): you can make the call more secure by revoking to the user all actions on the database and give him only the right to execute this stored procedure. If the procedure contains a where statement, the user will be able to see only the records corresponding to this condition. If the user had a select role to the table, he would be able to select all the rows in the table.
- Security (validating what comes from your program): you can make the call more secure by using logic to validate the parameters given in input.
- A stored procedure is DBMS dependent. If you change your database to another database than SQL server, the syntax below will not apply anymore.
- Difficult to debug and unit test: tools to debug or execute unit tests are not so convenient as for other languages like java.
Remember: the best way to make a decision is to try both options and measure which one is the best for your need and for what is in your database.