Create, Alter, Drop and Execute SQL Server Stored Procedures

Problem

As a beginning SQL Server Developer \ DBA, I have the skills to design and manually run T-SQL scripts.  However, I am less clear on how to package my T-SQL scripts for easy re-use by me and others.  Please provide examples that illustrate the basics of creating, altering, and running stored procedures to facilitate the re-use T-SQL code.  Also, briefly describe the use of input and output parameters as well as return code values associated with stored procedures.  Demonstrate a stored procedure that returns more than one result set based on the value of an input parameter.

Solution

This tip gives you a quick introduction to the basics of creating, dropping and altering stored procedures. You will also learn how to run a stored procedure to create a result set for viewing.

Many blocks of T-SQL code can be run from a stored procedure.  It is common to test the initial version of code inside a T-SQL script file and later copy the code into the body of a stored procedure shell.

After creating or modifying a stored procedure containing one or more SELECT statements, you can invoke the stored procedure with an EXEC statement.  Consequently, you can think of a stored procedure as a container that facilitates the re-use of T-SQL code within it.

Overview of SQL Server Stored Procedures

A stored procedure is a saved block of T-SQL code, such as a query to list the rows in a table.  A block of T-SQL code can be saved in a T-SQL script file.  You can also store the code from a script file in a stored procedure.

There are several benefits that result from saving code in a stored procedure rather than a script file.  These are some examples.

• You do not need to expose the code in a stored procedure in order to run its T-SQL code.  In contrast, users need to open a script file with its code in order to run the code.
• Stored procedures also offer a means of limiting access to the underlying tables for a query.  By granting access to run stored procedures without permission to read or write to the underlying tables, you can secure data but still allow visibility for data in the underlying tables through a stored procedure.
• You can use input parameters with stored procedures to vary the operation of the code inside a stored procedure.  While script files do allow the use of local variables to modify the return sets from queries, script files must expose their code to allow you to modify local variables at run time.
• By gaining proficiency in segmenting a programming solution into parts based on stored procedures, you make it easier to change code over time.  By adding code in short modular scripts, each script can be easier to read and maintain and even re-use in other applications.  Solutions based on SQL files with scripts for queries can become increasingly long, difficult to read, and maintain as successive changes continue to be made to a solution.

Stored procedures introduce a level of abstraction between the code for a solution and using the code that is not present when you maintain your code in a script file.  Therefore, if you have a simple solution that is used by one user who needs to have access to the underlying data sources for a query (or set of queries), then a script file may be better because it simplifies the solution.

Create a New SQL Server Stored Procedure

Many DBAs are familiar with creating a table via a CREATE TABLE statement.  Similarly, developer DBAs can also create a stored procedure with a CREATE PROC or CREATE PROCEDURE statement.  Just like the create table statement adds a table to a database so does the CREATE PROC statement add a stored procedure to a database.  So, you need to start with a new or existing database when you want to create a stored procedure, since the stored procedure is actually stored in the database.

If you have appropriate permissions, you can use a CREATE DATABASE statement to make a new database to hold tables and other kinds of objects, such as stored procedures. The create database statement is normally restricted to a few login accounts on a SQL Server instance. 

The following script creates a database named CodeModuleTypes.  Its first statement specifies the master database as the default database.  Its second statement creates the database.  It is possible to have much more elaborate versions of the CREATE DATABASE statement depending on your needs.  Unless you specify otherwise, a simple create database statement like the one below will utilize default settings from the model database, which is one of the standard databases that installs with SQL Server.

use master;
GO
 
create database CodeModuleTypes;

After you have a database, such as CodeModuleTypes, you can invoke a CREATE PROC statement within that database.  All stored procedures created in this tip are saved in the CodeModuleTypes database.

The following script demonstrates a syntax that you can use to create your first stored procedure.  The stored procedure in the code below displays a result set with all columns for each row from the Employee table in the HumanResources schema of the AdventureWorks2014 database.

You can think of a schema as a way to logically group database objects, such as tables and stored procedures.  These logical groupings avoid name conflicts between objects with the same name in different schema.  Any one database can have multiple schemas.  In this tip, all stored procedures are designated as belonging to the dbo schema of the CodeModuleTypes database.

The CREATE PROC statement below has three parts.

• The CREATE PROC statement names the stored procedure (and its schema if you are explicitly designating it).
• The as keyword acts as a marker to denote that the defining code for the stored procedure is about to start.
• The T-SQL code defining the operation of the stored procedure.  In this example, the defining code is the SELECT statement for the Employee table in the HumanResources schema of the AdventureWorks2014 database.  This prior MSSQLTips.com tip describes how to download a copy of the AdventureWorks2014 database.

use CodeModuleTypes;
go
 
create proc dbo.uspMyFirstStoredProcedure
as
select * 
from AdventureWorks2014.HumanResources.Employee;

After you create a stored procedure, you can run it with an EXEC statement like the one below.  It is this statement that returns the result set with all columns for each row from the Employee table.

exec dbo.uspMyFirstStoredProcedure

Here’s an excerpt from the output generated by the preceding script.

• The Results pane shows first eleven columns from the first seventeen rows of the 290 employees at the AdventureWorks company.
• If you wanted to process the rows displayed by a SELECT statement within a stored procedure, then you would need to store the result set rows in some other SQL Server table or object.  Next, process the results in that object.

Drop or Delete a SQL Server Stored Procedure

The preceding script to create a stored procedure will fail if the uspMyFirstStoredProcedure stored procedure in the dbo schema already exists.  One response to this issue is to drop the prior version of the stored procedure and then re-run the script to create the new version of the stored procedure.  You can remove the prior version of the uspMyFirstStoredProcedure stored procedure with a DROP PROC or DROP PROCEDURE statement.  The following line of T-SQL illustrates the use of the drop proc statement for the uspMyFirstStoredProcedure stored procedure.

drop proc dbo.uspMyFirstStoredProcedure

Instead of allowing the CREATE PROC statement to fail when there is a prior version of a stored proc, it is common practice to check if the stored procedure exists already and remove it to avoid an error before running the CREATE PROC statement for a new version of the stored procedure.  The following script can be used to drop a prior version of the uspMyFirstStoredProcedure stored procedure if it already exists.  You can run a script like this before invoking a CREATE PROC statement.  Depending on your requirements and the code defining the stored procedure, it may be beneficial to re-name the currently existing stored procedure instead of dropping it.

-- conditionally drop a stored proc
if object_id('dbo.uspMyFirstStoredProcedure') is not null 
     drop proc dbo.uspMyFirstStoredProcedure
go

Alter or Modify an Existing SQL Server Stored Procedure

The next code block demonstrates the ALTER PROC statement.  The ALTER PROC statement is different than the CREATE PROC statement in that the ALTER PROC statement can only operate on an existing stored procedure.

• This script is designed to run immediately after the preceding script that removes uspMyFirstStoredProcedure if it exists already.
• The first two statements in the following code block are CREATE PROC and EXEC statements that can create a fresh copy of the stored procedure and run uspMyFirstStoredProcedure.  Because all the code appears in single block, three GO keywords are required that would not be necessary if the whole code block was segmented into four separate blocks of code. 
  • The initial CREATE PROC statement needs to be trailed by a GO keyword so that the CREATE PROC statement completes before the first EXEC statement.
  • Then, the initial EXEC statement needs to be trailed by a GO keyword so that the ALTER PROC statement is the first statement in its batch.
  • Finally, the ALTER PROC statement needs to be followed by a GO keyword so that the ALTER PROC statement completes before the final EXEC statement.
• Just like the CREATE PROC statement, the ALTER PROC statement has three parts.
  • The object name after ALTER PROC must match the name of an existing stored procedure that you wish to change.
  • The as keyword acts as a delimiter separating ALTER PROC declarations from the new T-SQL code defining the modified version of uspMyFirstStoredProcedure.
  • The new code inside the ALTER PROC statement joins the Person table from the Person schema to the Employee table from the HumanResources schema in the AdventureWorks2014 database.

-- create a new stored proc
create proc dbo.uspMyFirstStoredProcedure
as
select * 
from AdventureWorks2014.HumanResources.Employee
go
 
-- run stored proc
exec dbo.uspMyFirstStoredProcedure
go
 
-- alter stored proc
alter proc dbo.uspMyFirstStoredProcedure
as
select
 Employee.BusinessEntityID
,Person.FirstName
,Person.LastName
,Employee.JobTitle
from AdventureWorks2014.HumanResources.Employee
inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
go
 
-- run altered stored proc
exec dbo.uspMyFirstStoredProcedure

Here’s the output from the EXEC statement in the preceding script.

• The BusinessEntityID and JobTitle columns are from the Employee table.
• The FirstName and LastName columns are from the Person table.

Aside from the obvious difference of creating a fresh stored procedure and modifying an existing stored procedure, the ALTER PROC statement is different from the CREATE PROC statement in other important ways.  For example, the ALTER PROC statement preserves all security settings associated with an existing stored proc, but the CREATE PROC statement does not preserve these settings.  In this way, the ALTER PROC statement is better than the CREATE PROC statement when all you want to do is change the code in an existing stored procedure.

Starting with SQL Server 2016 SP1, Microsoft introduced a new create or alter statement for code modules, such as stored procedures, views, and user defined functions.  You can get an introduction to the functionality of this new statement from this prior MSSQLTips.com tip.

SQL Server Stored Procedure Input Parameters

An input parameter allows a developer to vary how a stored procedure operates at run time.  It is common to use an input parameter within the where clause of a SELECT statement to control the rows that display when a stored procedure runs.  See this MSSQLTips.com tutorial page for demonstrations of how to use input parameters in a where clause.

Here is a simple example based on an alteration to uspMyFirstStoredProcedure that demonstrates the use of an input parameter.

• The input parameter named @jobtitle is named just before the as keyword.
• The input parameter is referenced in the where clause of the SELECT statement.
• The EXEC statement that trails the ALTER PROC statement assigns a value to the @jobtitle input parameter at run time.  The parameter value is a nvarchar string (Production Supervisor).

-- alter a stored proc-- this alteration has one select statement with a where clause
-- and a criterion set by an input parameter
-- and an input parameter
alter proc dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar(50)
as
 
select
 Employee.BusinessEntityID
,Person.FirstName
,Person.LastName
,Employee.JobTitle
from AdventureWorks2014.HumanResources.Employee
inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
where Employee.JobTitle like @jobtitle + '%'
go
 
-- run altered stored proc with 
-- @jobtitle parameter value is passed without naming the parameter
exec uspMyFirstStoredProcedure N'Production Supervisor'

Here’s the Results tab showing the output from the SELECT statement inside uspMyFirstStoredProcedure.  The tab shows the 21 employees whose job titles start with Production Supervisor.  You can change the contents of the Results tab by using a different literal string value in the EXEC statement.

SQL Server Stored Procedure Output Parameters

An output parameter passes a scalar value from a stored procedure to the EXEC statement calling it.  Some earlier MSSQLTips.com coverage of this topic appears in this tutorial page.  Additionally, a follow-up tip in this series on stored procedures will present numerous detailed examples demonstrating how to program input parameters, output parameters and return code values with stored procedures.

When you just want to pass a single value, such as a sum or count, from a stored procedure, you can do that with an output parameter.  The following ALTER PROC statement illustrates one way of implementing this kind of task.

• The ALTER PROC statement again changes uspMyFirstStoredProcedure.
• The input parameter (@jobtitle) from the prior version of the stored procedure is retained.
• Additionally, an output parameter specification is added before the as keyword.
  • The output parameter name is @jobtitlecount.
  • The data type for the parameter is int because it is meant to store a count value, but you could also use bigint as a data type if it were required.
  • The keyword out ends the parameter specification to indicate that this parameter returns a value after the stored procedure runs.
• The SELECT statement consists of a nested inner select statement in an outer select statement.
  • The inner select statement returns a row for each employee whose JobTitle starts with the value of the input parameter.
  • The outer select statement counts the number of rows returned by the inner select statement and assigns the count to the output parameter (@jobtitlecount).

-- alter a stored proc
-- this alteration computes an aggregate function value
-- based, in part, on an input parameter (@jobtitle)
-- and saves the computed value in an output parameter (@jobtitlecount)
alter proc dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar(50), @jobtitlecount int out
as
 
select @jobtitlecount = count(*)
from AdventureWorks2014.HumanResources.Employee
inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
where Employee.JobTitle like @jobtitle + '%'

After the ALTER PROC statement runs, you can invoke the freshly modified version of uspMyFirstStoredProcedure and display the value of the output parameter.  The following script shows how to achieve this.

• Before invoking the EXEC statement to run uspMyFirstStoredProcedure, declare a local variable (@jobtitlecount) to receive the output parameter value from the stored procedure.
  • The output parameter value appears in the EXEC statement with trailing output keyword (OUTPUT).  This keyword indicates the parameter value is passed from the stored procedure to the EXEC statement.
  • An assignment operator (=) passes the output parameter value to the @jobtitlecount local variable.
• A SELECT statement after the EXEC statement displays the value of the @jobtitlecount local variable which received the output parameter value.  For your easy reference, the output parameter value displayed by the preceding script is 21.

-- run an altered stored proc with -- @jobtitle input parameter value and
-- save the returned output parameter in a local variable
declare @jobtitlecount int
exec uspMyFirstStoredProcedure N'Production Supervisor',@jobtitlecount = @jobtitlecount OUTPUT
select @jobtitlecount [Job Title Count]

SQL Server Stored Procedure Return Code Values

Stored procedures can have return code values.  Return code values always have an int data type.

Here’s a script to set a return code of zero or one from inside a stored procedure.  If a search string criterion based on an input parameter exists in a column, then the return value is set to one.  Otherwise, the return value is set to zero.

• The input parameter has the name @jobtitle.
• The where clause criterion in the SELECT statement is: Employee.JobTitle like '%' + @jobtitle + '%'
• When the SELECT statement with the where clause returns at least one row, then the return value is set to one.  Otherwise, the return value is set to zero.
• An EXISTS condition determines if at least one row is returned or not from the SELECT statement.
• A return clause passes back a return code value and exits the stored procedure.

-- alter a stored proc
-- this alteration verifies if a search string value
-- is in a set of column values
-- @jobtitle input parameter contains the search string value
-- JobTitle is the column of values searched
alter proc dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar(50)
as
 
-- does at least one JobTitle contain @jobtitle?
if exists(
   select top 1 Employee.JobTitle
   from AdventureWorks2014.HumanResources.Employee
   inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
   where Employee.JobTitle like '%' + @jobtitle + '%'
)
begin
   return(1)
end
else
begin
   return(0)
end

The following script demonstrates the syntax for collecting a return code value from a stored procedure and shows some sample code for processing a return code value.  The script invokes uspMyFirstStoredProcedure for two different values of @jobtitle – either sals or sales.  No JobTitle column value contains sals, but at least one JobTitle contains sales.

• First, the code declares two local variables named @jobtitle and @exists.
  • The @jobtitle local variable is used in an EXEC statement to pass a search string to uspMyFirstStoredProcedure.
  • The @exists local variable is used to collect the return code value from  uspMyFirstStoredProcedure.  An assignment statement inside the EXEC statement populates the @exists local variable with the return code value.
  • An if…else control flow statement after the EXEC statement processes the return value from the stored procedure.
    • If @exists equals zero, a SELECT statement reports that there is no JobTitle with the search string value in the input parameter.
    • If @exists equals one, a SELECT statement reports that there is at least one JobTitle value with the input parameter.
• Below the stored procedure is executed twice.  The initial execution is for a search string value of sals.  The second execution is for a search string value of sales.

-- run an altered stored proc with 
-- @jobtitle is an input parameter
-- @exists equals 1 for at least 1 JobTitle containing @jobTitle
-- @exists equals 0 for no JobTitle containing @jobtitle
declare @jobtitle nvarchar(50), @exists int
 
set @jobtitle = 'sals'
exec @exists = uspMyFirstStoredProcedure @jobtitle
if @exists = 0
begin
   select 'No JobTitle values with ' + @jobtitle [search outcome]
end
else
begin
   select 'At least one JobTitle value with ' + @jobtitle [search outcome]
end
 
set @jobtitle = 'sales'
exec @exists = uspMyFirstStoredProcedure @jobtitle
if @exists = 0
begin
   select 'No JobTitle values with ' + @jobtitle [search outcome]
end
else
begin
   select 'At least one JobTitle value with ' + @jobtitle [search outcome]
end

Here’s the output from the preceding script.  You can use it to confirm the operation of the code to evaluate if at least one value in a column contains a search string.

Multiple result sets from a SQL Server Stored Procedure

The following script demonstrates again how to use an input parameter in an ALTER PROC statement.  The input parameter name before the as keyword is @jobtitle.  This demonstration is special in that it includes two separate SELECT statements.  The first SELECT statement returns a result set comprised of all rows whose JobTitle starts with the value in the input parameter.  The second SELECT statement returns a scalar value that is the count of the number of employees in the Employee table whose JobTitle starts with the input parameter value.

The EXEC statement after the ALTER PROC statement invokes uspMyFirstStoredProcedure. The nvarchar literal string value (Production Supervisor) after the stored procedure name is the input parameter value.

-- alter a stored proc-- this alteration has two select statements
-- and an input parameter
alter proc dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar(50)
as
 
-- 1st select statement returns a set of row values
select
   Employee.BusinessEntityID
  ,Person.FirstName
  ,Person.LastName
  ,Employee.JobTitle
from AdventureWorks2014.HumanResources.Employee
inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
where Employee.JobTitle like @jobtitle + '%'
 
-- 2nd select statement returns a scalar value
select count(*) as JobTitleCount
from AdventureWorks2014.HumanResources.Employee
inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
where Employee.JobTitle like @jobtitle + '%'

Here’s a short script to invoke the preceding stored procedure. 

-- run altered stored proc
-- @jobtitle parameter value is passed without naming the parameter
 
exec dbo.uspMyFirstStoredProcedure N'Production Supervisor'

Here’s the Results tab showing the output from the two SELECT statements inside the stored procedure named uspMyFirstStoredProcedure.  You can change the contents of the Results tab by using a different literal string value in the EXEC statement.

• The top pane shows the twenty-one employees whose job titles start with Production Supervisor.
• The bottom pane shows a scalar value with the count of employees whose job titles start with Production Supervisor.

Next Steps

• You can run the code for this tip on a computer with the AdventureWorks2014 database although other versions of the database are likely to yield the same results (if you update the from clause references from AdventureWorks2014 to whatever other version of the AdventureWorks database you use for testing this tip’s scripts).
• Next, copy the script that you want to test from this tip to confirm that you get the same result reported in the tip.
• Finally, modify the script to work in another database of your choice to start creating and running stored procedures with your scripts in your databases.