Skip to content

Latest commit

 

History

History
227 lines (146 loc) · 13.6 KB

execute-a-stored-procedure.md

File metadata and controls

227 lines (146 loc) · 13.6 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom f1_keywords helpviewer_keywords monikerRange
Execute a stored procedure
Learn how to execute a stored procedure by using SQL Server Management Studio or Transact-SQL.
WilliamDAssafMSFT
wiassaf
01/25/2024
sql
stored-procedures
how-to
ignite-2024
sql13.swb.executeprocedure.general.f1
sql13.swb.executeprocedure.f1
stored procedures [SQL Server], parameters
extended stored procedures [SQL Server], executing
system stored procedures [SQL Server], executing
stored procedures [SQL Server], executing
user-defined stored procedures [SQL Server]
>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

Execute a stored procedure

[!INCLUDE SQL Server Azure SQL Database Synapse Analytics PDW FabricSQLDB]

This article describes how to execute a stored procedure in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql].

There are different ways to execute a stored procedure. The first and most common approach is for an application or user to call the procedure. Another approach is to set the stored procedure to run automatically when an instance of [!INCLUDEssNoVersion] starts.

When a procedure is called by an application or user, the [!INCLUDEtsql] EXECUTE or EXEC keyword is explicitly stated in the call. The procedure can be called and executed without the EXEC keyword if the procedure is the first statement in a [!INCLUDEtsql] batch.

Limitations and restrictions

The calling database collation is used when matching system procedure names. For this reason, always use the exact case of system procedure names in procedure calls. For example, this code fails if executed in the context of a database that has a case-sensitive collation:

EXEC SP_heLP; -- Fails to resolve because SP_heLP doesn't equal sp_help

To display the exact system procedure names, query the sys.system_objects and sys.system_parameters catalog views.

If a user-defined procedure has the same name as a system procedure, the user-defined procedure might not ever execute.

Recommendations

Use the following recommendations for executing stored procedures.

System stored procedures

System procedures begin with the prefix sp_. Because they logically appear in all user- and system- defined databases, system procedures can be executed from any database without having to fully qualify the procedure name. However, it's best to schema-qualify all system procedure names with the sys schema name to prevent name conflicts. The following example shows the recommended method of calling a system procedure.

EXEC sys.sp_who;

User-defined stored procedures

When executing a user-defined procedure, it's best to qualify the procedure name with the schema name. This practice gives a small performance boost because the [!INCLUDEssDE] doesn't have to search multiple schemas. Using the schema name also prevents executing the wrong procedure if a database has procedures with the same name in multiple schemas.

The following examples demonstrate the recommended method to execute a user-defined procedure. This procedure accepts two input parameters. For information about specifying input and output parameters, see Specify parameters in a stored procedure.

EXECUTE SalesLT.uspGetCustomerCompany @LastName = N'Cannon', @FirstName = N'Chris';
GO

Or:

EXEC SalesLT.uspGetCustomerCompany N'Cannon', N'Chris';
GO

If a nonqualified user-defined procedure is specified, the [!INCLUDEssDE] searches for the procedure in the following order:

  1. The sys schema of the current database.

  2. The caller's default schema if the procedure executes in a batch or in dynamic SQL. If the nonqualified procedure name appears inside the body of another procedure definition, the schema that contains this other procedure is searched next.

  3. The dbo schema in the current database.

Security

For security information, see EXECUTE AS (Transact-SQL) and EXECUTE AS Clause (Transact-SQL).

Permissions

For permissions information, see Permissions in EXECUTE (Transact-SQL).

Stored procedure execution

You can use the SQL Server Management Studio (SSMS) user interface or [!INCLUDEtsql] in an SSMS query window to execute a stored procedure. Always use the latest version of SSMS.

Use SQL Server Management Studio

  1. In Object Explorer, connect to an instance of [!INCLUDEssNoVersion] or [!INCLUDEssazure-sqldb], expand that instance, and then expand Databases.

  2. Expand the database that you want, expand Programmability, and then expand Stored Procedures.

  3. Right-click the stored procedure that you want to run and select Execute Stored Procedure.

  4. In the Execute Procedure dialog box, Parameter indicates the name of each parameter, Data Type indicates its data type, and Output Parameter indicates whether it's an output parameter.

    For each parameter:

    • Under Value, type the value to use for the parameter.
    • Under Pass Null Value, select whether to pass a NULL as the value of the parameter.

  5. Select OK to execute the stored procedure. If the stored procedure doesn't have any parameters, just select OK.

    The stored procedure runs, and results appear in the Results pane.

    For example, to run the SalesLT.uspGetCustomerCompany stored procedure from the Create a stored procedure article, enter Cannon for the @LastName parameter and Chris for the @FirstName parameter, and select OK. The procedure returns FirstName Chris, LastName Cannon, and CompanyName Outdoor Sporting Goods.

Use [!INCLUDEtsql] in a query window

  1. In SSMS, connect to an instance of [!INCLUDEssNoVersion] or [!INCLUDEssazure-sqldb].

  2. From the toolbar, select New Query.

  3. Enter an EXECUTE statement with the following syntax into the query window, providing values for all expected parameters:

    EXECUTE <ProcedureName> N'<Parameter 1 value>, N'<Parameter x value>;  
GO

    For example, the following [!INCLUDEtsql] statement executes the uspGetCustomerCompany stored procedure and with Cannon as the @LastName parameter value and Chris as the @FirstName parameter value:

    EXEC SalesLT.uspGetCustomerCompany N'Cannon', N'Chris';
GO

  4. From the toolbar, select Execute. The stored procedure runs.

Options for parameter values

There are multiple ways to provide parameters and values in stored procedure EXECUTE statements. The following examples show several different options for the EXECUTE statement.

  • If you provide the parameter values in the same order as they're defined in the stored procedure, you don't need to state the parameter names. For example:

    EXEC SalesLT.uspGetCustomerCompany N'Cannon', N'Chris';

  • If you provide parameter names in the @parameter_name=value pattern, you don't have to specify the parameter names and values in the same order as they're defined. For example, either of the following statements are valid:

    EXEC SalesLT.uspGetCustomerCompany @FirstName = N'Chris', @LastName = N'Cannon';

    or:

    EXEC SalesLT.uspGetCustomerCompany @LastName = N'Cannon', @FirstName = N'Chris';

  • If you use the @parameter_name=value form for any parameter, you must use it for all subsequent parameters in that statement. For example, you can't use EXEC SalesLT.uspGetCustomerCompany1 @FirstName = N'Chris', N'Cannon';.

Automatic execution at startup

Applies to: [!INCLUDEssNoVersion]

In [!INCLUDEssNoVersion], a member of the sysadmin server role can use sp_procoption to set or clear a procedure for automatic execution at startup. Startup procedures must be in the master database, must be owned by sa, and can't have input or output parameters. For more information, see sp_procoption (Transact-SQL).

Procedures marked for automatic execution at startup execute every time [!INCLUDEssNoVersion] starts and the master database is recovered during that startup process. Setting up procedures to execute automatically can be useful for performing database maintenance operations or for having procedures run continuously as background processes.

Another use for automatic execution is to have the procedure perform system or maintenance tasks in tempdb, such as creating a global temporary table. Automatic execution ensures that such a temporary table always exists when tempdb is recreated during [!INCLUDEssNoVersion] startup.

An automatically executed procedure operates with the same permissions as members of the sysadmin fixed server role. Any error messages generated by the procedure write to the [!INCLUDEssNoVersion] error log.

There's no limit to the number of startup procedures you can have, but each startup procedure consumes one worker thread while executing. If you need to execute multiple procedures at startup but don't need to execute them in parallel, make one procedure the startup procedure and have that procedure call the other procedures. This method uses only one worker thread.

Tip

Don't return any result sets from a procedure that's executed automatically. Because the procedure is being executed by [!INCLUDEssNoVersion] instead of an application or user, there's nowhere for result sets to go.

Note

Azure SQL Database is designed to isolate features from dependencies on the master database. As such, [!INCLUDEtsql] statements that configure server-level options aren't available in Azure SQL. You can often find appropriate alternatives from other Azure services such as Elastic jobs or Azure Automation.

Set a procedure to execute automatically at startup

Only the system administrator (sa) can mark a procedure to execute automatically.

  1. In SSMS, connect to the [!INCLUDEssDE].

  2. From the Standard toolbar, select New Query.

  3. Enter the following sp_procoption commands to set a stored procedure to automatically execute at [!INCLUDEssNoVersion] startup.

    EXEC sp_procoption @ProcName = N'<stored procedure name>'   
    , @OptionName = 'startup'   
    , @OptionValue = 'on';
GO

  4. In the toolbar, select Execute.

Stop a procedure from executing automatically at startup

A sysadmin can use sp_procoption to stop a procedure from automatically executing at [!INCLUDEssNoVersion] startup.

  1. In SSMS, connect to the [!INCLUDEssDE].

  2. From the Standard toolbar, select New Query.

  3. Enter the following commands into the query window.

    EXEC sp_procoption @ProcName = N'<stored procedure name>'      
    , @OptionName = 'startup'
    , @OptionValue = 'off';
GO

  4. In the toolbar, select Execute.

Related content