Use stored procedures with SQL server

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:

Goal:

We want to execute the following statement using a stored procedure:

select
first_name,
last_name,
phone_number
from clients
where
state='karnataka'

Stored procedure creation:

Use the following syntax to create the corresponding stored procedure:

create proc
searchClientByState
@state varchar(30)
as
select
first_name,
last_name,
phone_number
from clients
where
state=@state

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:

Explanation:

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 [email protected] 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:

exec searchClientByState 'Karnataka'

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:

grant execute on searchClientByState
to arjunj

Dropping a stored procedure:

To drop the stored procedure, use the keywords drop proc. Example: to drop the stored procedure called searchClientByState :

drop proc
searchClientByState

Raising a specific error if a condition fulfilled:

Goal:

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).

Declaration:

create proc
searchClientByState
@state varchar(30)
as
if @state=''
begin
raiserror

(
'Empty state provided',
12,1
)
return
end


if isnumeric(@state) = 1
begin
raiserror
(
'State cannot be numeric'
,12,2
)
return
end


select
first_name,
last_name,
phone_number
from clients
where [email protected]
if @@ROWCOUNT = 0
begin
raiserror
(
'no record for state:%s',
12,3,@state
)
end

Explanation:

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:

Empty state:

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

State numeric:

exec searchClientByState 123

Msg 50000, Level 12, State 3, Procedure searchClientByState, Line 88
State cannot be numeric

Stored procedure giving a parameter as output:

Goal:

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.

Declaration:

create proc
searchClientById
@id int,
@first_name
varchar(30) output,
@last_name
varchar(30) output,
@phone_number
int output
as
select
@first_name=first_name,
@last_name=last_name,
@phone_number=phone_number
from clients
where [email protected]

Explanation:

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),
@last_name varchar(30),
@phone_number int,
@id int


select @id=1
exec searchClientById @id,
@first_name output,
@last_name output,
@phone_number output


select @first_name,
@last_name,
@phone_number

Explanation:

  • 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
begin
drop procedure
searchClientByState
end

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:

CREATE LOGIN vincent
WITH PASSWORD =
'helloThisIsMyPassword!';
GO
CREATE USER vincent
FOR LOGIN vincent;
GO

Secondly, we create the role and assign someone to administer this role:

CREATE ROLE viewers AUTHORIZATION vincent;

We assign this role to existing users:

EXEC sp_addrolemember
'viewers',
'arjunj'
EXEC sp_addrolemember
'viewers',
'suneinar '

Finally, we grant to the role the right to execute the stored procedure:

GRANT EXECUTE
ON searchClientByState
TO viewers

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.

Disadvantages:

  • 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.

 
Please help this site survive, share on:



Author:
Last udpated: September 10, 2017
Created: February 15, 2015

Something not clear? Ask your question here:

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
 

3 questions on “Use stored procedures with SQL server

  1. Hi is there anyway you could write something up for user defined functions and triggers? I’m struggling with them and am wondering if you could help.

    • Hi Victoria,
      Thank you for commenting :) Yes I can write something up on user defined functions and triggers. On which kind of database do you plan to work? Oracle, Mysql, sql server? Regarding triggers, the principle of triggers is that upon an action on the database (insert, update or delete), you execute another action on the database (insert, update, delete, raise and error, send a mail) on the same table or on another table (for example insert a row in a log table). If you need some information from the action that triggered the second action (for example name of the customer in the row that was inserted), you refer to this table as “inserted” (for update, you use the keyword “updated”).
      Example inserting a row in MY_LOG_TABLE when an insert is executed on MY_TABLE:
      CREATE TRIGGER myTrigger ON MY_TABLE
      FOR INSERT
      AS
      begin
      insert into
      MY_LOG_TABLE
      (first_name,last_name)
      SELECT
      i.first_name, i.first_name
      FROM
      inserted i
      end

      Kind regards
      Vincent