Skip to content

Latest commit

 

History

History
74 lines (54 loc) · 2.47 KB

sp-prepexec-transact-sql.md

File metadata and controls

74 lines (54 loc) · 2.47 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sp_prepexec (Transact-SQL)
sp_prepexec prepares and executes a parameterized Transact-SQL statement.
markingmyname
maghan
randolphwest
03/07/2025
sql
system-objects
reference
sp_cursor_prepexec
sp_cursor_prepexec_TSQL
sp_prepexec
TSQL

sp_prepexec (Transact-SQL)

[!INCLUDE SQL Server]

Prepares and executes a parameterized [!INCLUDE tsql] statement. sp_prepexec combines the functions of sp_prepare and sp_execute. This action is invoked by ID = 13 in a tabular data stream (TDS) packet.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_prepexec handle OUTPUT , params , stmt
    [ , bound param ] [ , ...n ]
[ ; ]

Arguments

[!INCLUDE extended-stored-procedures]

handle

The [!INCLUDE ssNoVersion]-generated handle identifier. handle is a required parameter with an int return value.

params

Identifies parameterized statements. The params definition of variables is substituted for parameter markers in the statement. params is a required parameter that calls for an ntext, nchar, or nvarchar input value. Input a NULL value if the statement isn't parameterized.

stmt

Defines the cursor result set. The stmt parameter is required and calls for an ntext, nchar, or nvarchar input value.

bound_param

Signifies the optional use of extra parameters. bound_param calls for an input value of any data type to designate the extra parameters in use.

Examples

The following example prepares and executes a simple statement:

Declare @Out int;
EXEC sp_prepexec @Out output,
    N'@P1 nvarchar(128), @P2 nvarchar(100)',
    N'SELECT database_id, name
      FROM sys.databases
      WHERE name=@P1 AND state_desc = @P2',
          @P1 = 'tempdb', @P2 = 'ONLINE';
EXEC sp_unprepare @Out;

Related content