Microsoft® SQL Server® 2012 Bible

437

Chapter 17: Developing Stored Procedures

17

Alternatively, you can use the OpenQuery() function to call a remote stored procedure:

OpenQuery(LinkedServerName, 'EXECUTE Schema.StoredProcedureName');

As with any other distributed query, the Distributed Transaction Coordinator service must

be running if the transaction updates data on more than one server.

System Stored Procedures

SQL Server provides many stored procedures used for administrative tasks and to gather

information about the database or server. Each of these system stored procedures is pre-

fi xed with sp_, and is the reason why you should never name a user-defi ned stored proce-

dure with a sp_ prefi x.

There is a wealth of information and functionality within these system stored procedures.

Check out Books Online for documentation and use cases.

Passing Data to Stored Procedures

The characteristic of a stored procedure that makes it a powerful option for code reuse is

parameterization. Stored procedures can be defi ned so that they accept input parameters.

The Sales.uspGetCurrencyInformation procedure created in the previous section

returned all rows from Sales.Currency. An input parameter can specify which rows

should be returned or can completely change the logic of the stored procedure based on

how it is coded.

As of SQL Server 2005, the number of parameters that can be specifi ed for a single stored

procedure is 2,100.

Input Parameters

Input parameters are a way to provide external information to a stored procedure. You can

add input parameters that pass data to the stored procedure by listing the parameters after

the procedure name in the CREATE PROCEDURE command. Each parameter must begin

with an @ sign and becomes a local variable within the procedure. Like local variables,

the parameters must be defi ned with valid data types. When the stored procedure is called,

every parameter value must be included (unless the parameter has a default value).

The following code sample creates a stored procedure that returns a single currency type.

The @CurrencyCode parameter can accept a character value with a length of up to three

characters. The value assigned to the parameter during stored procedure execution is

available within the procedure as the local variable @CurrencyCode in the WHERE clause

of the query:

USE AdventureWorks2012

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

http://www.it-ebooks.info