sp_prepexec를 활용한 미리 컴파일된 쿼리 사용하기

sp_prepexec

Used to prepare and execute a parameterized SQL statement. This command combines the functions of the sp_prepare and sp_execute procedures and is available from SQL2000 onwards.

Syntax

sp_prepexec [@handle =] statement_handle OUTPUT,
     [@paramdef =] N'parameter_name data_type, [,...n]'
     [@stmt =] N'stmt',
    {, [@param1 =] value1 [,...n] }

Arguments

[@handle =] statement_handle

Is the name of a declared integer variable to receive the statement handle. statement_handle is int, with no default.

[@paramdef =] N'parameter_name data_type [,...n]'

Is one string that contains the definitions of all parameters that have been embedded in stmt. Each parameter definition consists of a parameter name and a data type. n is a placeholder indicating additional parameter definitions. Every parameter specified in stmt must be defined in @paramdef.

[@stmt =] 'stmt'

Is a string containing a valid SQL statement. The size of the string is limited only by available database server memory. stmt can contain parameters having the same form as a variable name, for example:

'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'

Each parameter included in stmt must have a corresponding entry in both the @paramdef parameter definition list.

[@param1 =] value1

Is a value for the first parameter defined in the parameter string. The value can be a constant or a variable. There must be a parameter value supplied for every parameter included in stmt. The values are not needed if the Transact-SQL statement in stmt has no parameters.

n

Is a placeholder for the values of additional parameters. Values can be only constants or variables. Values cannot be more complex expressions such as functions, or expressions built using operators.

Return Code Values

0 (success) or 1 (failure).

Result Sets

Returns a result set if specified by the prepared statement.

Permissions

Execute permissions default to the public role.

Example

 
참고자료
http://cafe.naver.com/sloteadyfun.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=57
http://blogs.msdn.com/boduff/archive/2007/11/11/jdbc-when-good-prepared-statements-go-bad.aspx