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

GO


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

GO

 

-- OR --

 

USE EBusiness

GO

 

EXEC dbo.usp_EmployeeList

GO


Query Output Screen



* * * * *


Email Your Comment To AUTHOR