X hits on this document

118 views

0 shares

0 downloads

0 comments

42 / 48

676

CHAPTER 28 PERFORMANCE TUNING

The syntax for sp_executesql is as follows:

sp_executesql [ @stmt = ] stmt [ {, [@params=] N'@parameter_name data_type [ [ OUTPUT ][,...n]' } {, [ @param1 = ] 'value1' [ ,...n ] }

]

The arguments of this command are described in Table 28-6.

Table 28-6. sp_executesql Arguments Argument

stmt @ p a r a m e t e r _ n a m e d a t a _ t y p e [ [ O U T P U T ] [ , . . . n ]

Description The string to be executed.

One or more parameters that are embedded in the string statement. OUTPUT is used similarly to a stored procedure OUTPUT parameter.

'

value1' [ ,...n ]

The actual values passed to the parameters.

TransactionID

ProductID

TransactionType

Quantity

28345

813

S

7

31177

813

S

9

35796

813

S

6

36112

813

S

7

40765

813

S

6

47843

813

S

7

69114

813

S

6

73432

813

S

6

In this example, the Production.TransactionHistoryArchive table is queried based on a spe- cific ProductID, TransactionType, and minimum Quantity values:

EXECUTE sp_executesql N'SELECT TransactionID, ProductID,

This returns the following results:

How It Works

The sp_executesql allows you to execute a dynamically generated Unicode string. This system pro- cedure allows parameters, which in turn allow SQL Server to re-use the query execution plan generated by its execution.

FROM WHERE

TransactionType, Quantity Production.TransactionHistoryArchive ProductID = @ProductID AND TransactionType = @TransactionType AND Quantity > @Quantity', N'@ProductID int,

@TransactionType char(1), @Quantity int', @ProductID =813, @TransactionType = @Quantity = 5 'S',

Document info
Document views118
Page views118
Page last viewedFri Dec 09 12:18:21 UTC 2016
Pages48
Paragraphs987
Words15822

Comments