Execute a stored procedure
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
This article describes how to execute a stored procedure in SQL Server by using SQL Server Management Studio or Transact-SQL.
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 SQL Server starts.
When a procedure is called by an application or user, the Transact-SQL 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 Transact-SQL 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 Database Engine 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 one input parameter. For information about specifying input and output parameters, see Specify parameters in a stored procedure.
EXECUTE dbo.uspLogError @ErrorLogID = 1;
GO
Or:
EXEC AdventureWorksLT.dbo.uspLogError 1;
GO
If a nonqualified user-defined procedure is specified, the Database Engine searches for the procedure in the following order:
The
sysschema of the current database.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.
The
dboschema 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 Transact-SQL in an SSMS query window to execute a stored procedure. Always use the latest version of SSMS.
Use SQL Server Management Studio
In SSMS Object Explorer, connect to an instance of the SQL Server Database Engine, expand that instance, and then expand Databases.
Expand the database that you want, expand Programmability, and then expand Stored Procedures.
Right-click the user-defined stored procedure that you want and select Execute Stored Procedure.
In the Execute Procedure dialog box, specify values for any parameters and whether they should pass null values.
- Parameter indicates the name of the parameter.
- Data Type indicates the data type of the parameter.
- Output Parameter indicates if the parameter is an output parameter.
- Pass Null Value: Specify whether to pass a NULL as the value of the parameter.
- Value: Type the value to use for the parameter when calling the procedure.
Select OK to execute the stored procedure.
Use Transact-SQL in a query window
In SSMS, connect to the Database Engine.
From the Standard toolbar, select New Query.
Enter the following statements into the query window:
EXEC <stored procedure name> <parameter 1 value>, <parameter n value>; GOIn the toolbar, select Execute.
The following example shows a Transact-SQL statement for executing a stored procedure that expects one parameter. The example executes the uspLogError stored procedure with 1 specified as the @ErrorLogID parameter value.
EXEC dbo.uspLogError 1;
GO
Automatic execution at startup
Applies to: SQL Server
In SQL Server, 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 SQL Server 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 SQL Server 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 SQL Server 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 SQL Server 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, Transact-SQL 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 (preview) or Azure Automation.
Set a procedure to execute automatically at startup
Only the system administrator (sa) can mark a procedure to execute automatically.
In SSMS, connect to the Database Engine.
From the Standard toolbar, select New Query.
Enter the following sp_procoption commands to set a stored procedure to automatically execute at SQL Server startup.
EXEC sp_procoption @ProcName = N'<stored procedure name>' , @OptionName = 'startup' , @OptionValue = 'on'; GOIn 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 SQL Server startup.
In SSMS, connect to the Database Engine.
From the Standard toolbar, select New Query.
Enter the following commands into the query window.
EXEC sp_procoption @ProcName = N'<stored procedure name>' , @OptionName = 'startup' , @OptionValue = 'off'; GOIn the toolbar, select Execute.
Related content
Feedback
Submit and view feedback for