Microsoft® SQL Server® 2012 Bible

436

Part IV: Programming with T-SQL in SQL Server

USE AdventureWorks2012

GO

ALTER PROCEDURE Sales.uspGetCurrencyInformation

WITH ENCRYPTION

AS

SELECT CurrencyCode, Name, ModifiedDate

FROM Sales.Currency;

GO

A user with proper object permissions can still execute encrypted stored procedures;

however, the text of the code is not accessible to the user.

Executing Stored Procedures

You can call a stored procedure in three ways:

Sales.uspGetCurrencyInformation;

EXECUTE Sales.uspGetCurrencyInformation;

EXEC Sales.uspGetCurrencyInformation;

If the stored procedure call is the fi rst statement in a batch, the EXECUTE (or the abbrevi-

ated EXEC) is not required. If the stored procedure call is not the fi rst statement in a batch,

the EXECUTE or EXEC command is required. It is a good practice to always use EXECUTE or

EXEC in case code is reordered later and so that it is obvious to a developer or DBA that a

stored procedure is being executed.

WITH RESULT SETS

SQL Server 2012 introduces a new clause called WITH RESULT SETS. This enables one or more result

sets to be explicitly defi ned as part of the EXECUTE command. The “Returning Data from Stored

Procedures” section covers this feature.

Executing Remote Stored Procedures

Two methods exist for calling a stored procedure located on another server: a four-part

name reference and a distributed query. Both methods require that the remote server be

a linked server. Stored procedures may only be remotely called; they may not be remotely

created.

The remote stored procedure may be executed by means of the four-part name:

EXECUTE Server.Database.Schema.StoredProcedureName;

c17.indd 436c17.indd 436 7/30/2012 5:39:35 PM7/30/2012 5:39:35 PM

http://www.it-ebooks.info