Create, Alter, Drop and Execute SQL Server Stored Procedures (2024)

By: Rick Dobson |Comments (2) | Related: > Stored Procedures

Problem

As a beginning SQL Server Developer \ DBA, I have the skills to design and manually run T-SQLscripts. However, I am less clear on how to package my T-SQL scripts for easyre-use by me and others. Please provide examples that illustrate the basicsof creating, altering, and running stored procedures to facilitate the re-use T-SQLcode. Also, briefly describe the use of input and output parameters as wellas return code values associated with stored procedures. Demonstrate a storedprocedure 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 andaltering stored procedures. You will also learn how to run a stored procedure tocreate a result set for viewing.

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

After creating or modifying a stored procedure containing one or more SELECTstatements, 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 ofT-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 therows 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 procedurerather than a script file. These are some examples.

• You do not need to expose the code in a stored procedure in order to runits T-SQL code. In contrast, users need to open a script file with itscode in order to run the code.
• Stored procedures also offer a means of limiting access to the underlyingtables for a query. By granting access to run stored procedures withoutpermission to read or write to the underlying tables, you can secure data butstill allow visibility for data in the underlying tables through a stored procedure.
• You can use input parameters with stored procedures to vary the operationof the code inside a stored procedure. While script files do allow theuse of local variables to modify the return sets from queries, script filesmust expose their code to allow you to modify local variables at run time.
• By gaining proficiency in segmenting a programming solution into parts basedon stored procedures, you make it easier to change code over time. Byadding code in short modular scripts, each script can be easier to read andmaintain and even re-use in other applications. Solutions based on SQLfiles 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 solutionand 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 haveaccess to the underlying data sources for a query (or set of queries), then a scriptfile 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 PROCor CREATE PROCEDURE statement. Just like the create table statement adds atable to a database so does the CREATE PROC statement add a stored procedure toa database. So, you need to start with a new or existing database when youwant to create a stored procedure, since the stored procedure is actually storedin the database.

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

The following script creates a database named CodeModuleTypes. Its firststatement specifies the master database as the default database. Its secondstatement creates the database. It is possible to have much more elaborateversions of theCREATE DATABASE statement depending on your needs. Unless you specify otherwise, a simple create database statement like the one belowwill utilize default settings from the model database, which is one of the standarddatabases that installs with SQL Server.

use master;GO create database CodeModuleTypes;

After you have a database, such as CodeModuleTypes, you can invoke a CREATEPROCstatement within that database. All stored procedures created in this tipare saved in the CodeModuleTypes database.

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

You can think of a schema as a way to logically group database objects, suchas tables and stored procedures. These logical groupings avoid name conflictsbetween objects with the same name in different schema. Any one database canhave multiple schemas. In this tip, all stored procedures are designated asbelonging 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 ifyou are explicitly designating it).
• The as keyword acts as a marker to denote that the defining code for thestored procedure is about to start.
• The T-SQL code defining the operation of the stored procedure. Inthis example, the defining code is the SELECT statement for the Employee tablein the HumanResources schema of the AdventureWorks2014 database. Thisprior MSSQLTips.com tip describes how to download a copy of the AdventureWorks2014 database.

use CodeModuleTypes;go create proc dbo.uspMyFirstStoredProcedureasselect * from AdventureWorks2014.HumanResources.Employee;

After you create a stored procedure, you can run it with an EXEC statement likethe one below. It is this statement that returns the result set with all columnsfor 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 rowsof the 290 employees at the AdventureWorks company.
• If you wanted to process the rows displayed by a SELECT statement withina stored procedure, then you would need to store the result set rows in someother 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 uspMyFirstStoredProcedurestored procedure in the dbo schema already exists. One response to this issueis to drop the prior version of the stored procedure and then re-run the scriptto create the new version of the stored procedure. You can remove the priorversion of the uspMyFirstStoredProcedure stored procedure with a DROP PROC orDROP PROCEDURE statement.The following line of T-SQL illustrates the use of the drop proc statement for theuspMyFirstStoredProcedure stored procedure.

drop proc dbo.uspMyFirstStoredProcedure

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

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

Alter or Modify an Existing SQL Server Stored Procedure

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

• This script is designed to run immediately after the preceding script thatremoves uspMyFirstStoredProcedure if it exists already.
• The first two statements in the following code block are CREATE PROC andEXEC 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 requiredthat would not be necessary if the whole code block was segmented into fourseparate blocks of code.
  • The initial CREATE PROC statement needs to be trailed by a GO keywordso that the CREATE PROC statement completes before the first EXEC statement.
  • Then, the initial EXEC statement needs to be trailed by a GO keywordso that the ALTER PROC statement is the first statement in its batch.
  • Finally, the ALTER PROC statement needs to be followed by a GO keywordso that the ALTER PROC statement completes before the final EXEC statement.
• Just like the CREATE PROC statement, the ALTER PROC statement has threeparts.
  • The object name after ALTER PROC must match the name of an existingstored procedure that you wish to change.
  • The as keyword acts as a delimiter separating ALTER PROC declarationsfrom the new T-SQL code defining the modified version of uspMyFirstStoredProcedure.
  • The new code inside the ALTER PROC statement joins the Person tablefrom the Person schema to the Employee table from the HumanResources schemain the AdventureWorks2014database.

-- create a new stored proccreate proc dbo.uspMyFirstStoredProcedureasselect * from AdventureWorks2014.HumanResources.Employeego -- run stored procexec dbo.uspMyFirstStoredProcedurego -- alter stored procalter proc dbo.uspMyFirstStoredProcedureasselect Employee.BusinessEntityID,Person.FirstName,Person.LastName,Employee.JobTitlefrom AdventureWorks2014.HumanResources.Employeeinner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityIDgo -- run altered stored procexec 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 modifyingan existing stored procedure, the ALTER PROC statement is different from theCREATE PROC statement in other important ways. For example, the ALTER PROC statementpreserves all security settings associated with an existing stored proc, but theCREATE PROC statement does not preserve these settings. In this way, theALTER PROC statement is better than the CREATE PROC statement when all you want to dois change the code in an existing stored procedure.

Starting with SQL Server 2016 SP1, Microsoft introduced a new create or alterstatement 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 thisprior MSSQLTips.com tip.

SQL Server Stored Procedure Input Parameters

An input parameter allows a developer to vary how a stored procedure operatesat run time. It is common to use an input parameter within the where clauseof a SELECT statement to control the rows that display when a stored procedure runs.See thisMSSQLTips.com tutorial pagefor demonstrations of how to use input parameters in a where clause.

Here is a simple example based on an alteration to uspMyFirstStoredProcedurethat 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 valueto 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 parameteralter proc dbo.uspMyFirstStoredProcedure@jobtitle nvarchar(50)as select Employee.BusinessEntityID,Person.FirstName,Person.LastName,Employee.JobTitlefrom AdventureWorks2014.HumanResources.Employeeinner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityIDwhere Employee.JobTitle like @jobtitle + '%'go -- run altered stored proc with -- @jobtitle parameter value is passed without naming the parameterexec uspMyFirstStoredProcedure N'Production Supervisor'

Here’s the Results tab showing the output from the SELECT statement insideuspMyFirstStoredProcedure. The tab shows the 21 employees whose jobtitles start with Production Supervisor. You can change the contents of theResults 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 EXECstatement calling it. Some earlier MSSQLTips.com coverage of this topic appearsin thistutorial page. Additionally,a follow-up tip in this series on stored procedures will present numerous detailedexamples demonstrating how to program input parameters, output parameters and returncode values with stored procedures.

When you just want to pass a single value, such as a sum or count, from a storedprocedure, you can do that with an output parameter. The following ALTERPROCstatement 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 procedureis 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 storea 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 thisparameter returns a value after the stored procedure runs.
• The SELECT statement consists of a nested inner select statement in an outerselect statement.
  • The inner select statement returns a row for each employee whose JobTitlestarts with the value of the input parameter.
  • The outer select statement counts the number of rows returned by theinner 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 outas select @jobtitlecount = count(*)from AdventureWorks2014.HumanResources.Employeeinner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityIDwhere Employee.JobTitle like @jobtitle + '%'

After the ALTER PROC statement runs, you can invoke the freshly modified versionof 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, declarea local variable (@jobtitlecount) to receive the output parameter value fromthe stored procedure.
  • The output parameter value appears in the EXEC statement with trailingoutput keyword (OUTPUT). This keyword indicates the parameter valueis 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 @jobtitlecountlocal variable which received the output parameter value. For your easyreference, 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 variabledeclare @jobtitlecount intexec uspMyFirstStoredProcedure N'Production Supervisor',@jobtitlecount = @jobtitlecount OUTPUTselect @jobtitlecount [Job Title Count]

SQL Server Stored Procedure Return Code Values

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

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

• The input parameter has the name @jobtitle.
• The where clause criterion in the SELECT statement is: Employee.JobTitlelike '%' + @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 setto zero.
• An EXISTS condition determines if at least one row is returned or not fromthe 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 searchedalter 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)endelsebegin return(0)end

The following script demonstrates the syntax for collecting a return code valuefrom a stored procedure and shows some sample code for processing a return codevalue. The script invokes uspMyFirstStoredProcedure for two different valuesof @jobtitle – either sals or sales. No JobTitle column value containssals, 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 asearch string to uspMyFirstStoredProcedure.
  • The @exists local variable is used to collect the return code valuefrom uspMyFirstStoredProcedure. An assignment statement insidethe EXEC statement populates the @exists local variable with the returncode value.
  • An if...else control flow statement after the EXEC statement processesthe return value from the stored procedure.
    • If @exists equals zero, a SELECT statement reports that there isno JobTitle with the search string value in the input parameter.
    • If @exists equals one, a SELECT statement reports that there isat least one JobTitle value with the input parameter.
• Below the stored procedure is executed twice. The initial execution is fora search string value of sals. The second execution is for a search stringvalue 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 @jobtitledeclare @jobtitle nvarchar(50), @exists int set @jobtitle = 'sals'exec @exists = uspMyFirstStoredProcedure @jobtitleif @exists = 0begin select 'No JobTitle values with ' + @jobtitle [search outcome]endelsebegin select 'At least one JobTitle value with ' + @jobtitle [search outcome]end set @jobtitle = 'sales'exec @exists = uspMyFirstStoredProcedure @jobtitleif @exists = 0begin select 'No JobTitle values with ' + @jobtitle [search outcome]endelsebegin select 'At least one JobTitle value with ' + @jobtitle [search outcome]end

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

Multiple result sets from a SQL Server Stored Procedure

The following script demonstrates again how to use an input parameter in anALTER 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 JobTitlestarts with the value in the input parameter. The second SELECT statementreturns a scalar value that is the count of the number of employees in the Employeetable 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 procedurename is the input parameter value.

-- alter a stored proc-- this alteration has two select statements-- and an input parameteralter proc dbo.uspMyFirstStoredProcedure@jobtitle nvarchar(50)as -- 1st select statement returns a set of row valuesselect Employee.BusinessEntityID ,Person.FirstName ,Person.LastName ,Employee.JobTitlefrom AdventureWorks2014.HumanResources.Employeeinner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityIDwhere Employee.JobTitle like @jobtitle + '%' -- 2nd select statement returns a scalar valueselect count(*) as JobTitleCountfrom AdventureWorks2014.HumanResources.Employeeinner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityIDwhere Employee.JobTitle like @jobtitle + '%'

Here’s a short script to invoke the precedingstored 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 statementsinside the stored procedure named uspMyFirstStoredProcedure. You can changethe contents of the Results tab by using a different literal string value in theEXEC statement.

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

Next Steps

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

About the author