Programming in MS SQL Server 2012

EXECUTE Stored Procedure

The Transact-SQL EXECUTE statement is used to execute a Stored Procedure.

Execute System Stored Procedure

System stored procedures begin with the prefix sp_. They logically appear in system and all user-defined databases; they can be executed from any database without having to fully quality the procedure name. Yet, schema-qualifying all system procedure names with the sys schema name to prevent name conflicts. The following example describes the recommended method of calling a system stored procedure.

-- Example 99 --

EXEC sys.sp_who

Query Output Screen

Execute User-defined Stored Procedure

Every user-defined database has a default dbo schema. Database may have more than one schema names apart from default dbo schema. When execute a user-defined stored procedure, it is good to defined qualifying the stored procedure name with the particular schema name. This practice gives a small performance boost, because the Database Engine does not have to search multiple schemas. It also prevents executing the wrong stored procedure if a database has stored procedures with the same name in multiple schemas.

The following example describes different way to execute an existing Stored Procedure.

-- Example 100 --

EXEC EBusiness.dbo.usp_EmployeeList

-- OR --

USE EBusiness

EXEC dbo.usp_EmployeeList

Query Output Screen

* * * * *

Your Comment To AUTHOR