Skip to content

sys-sp-cdc-generate-wrapper-function-transact-sql.md

Latest commit

 

History

History
112 lines (78 loc) · 4.93 KB

sys-sp-cdc-generate-wrapper-function-transact-sql.md

File metadata and controls

112 lines (78 loc) · 4.93 KB
title sys.sp_cdc_generate_wrapper_function (Transact-SQL)
description Generates scripts to create wrapper functions for the change data capture query functions that are available in SQL Server.
author markingmyname
ms.author maghan
ms.reviewer wiassaf, randolphwest
ms.date 06/23/2025
ms.service sql
ms.subservice system-objects
ms.topic reference
f1_keywords
sp_cdc_generate_wrapper_function_TSQL
sp_cdc_generate_wrapper_function
sys.sp_cdc_generate_wrapper_function_TSQL
sys.sp_cdc_generate_wrapper_function
helpviewer_keywords
sys.sp_cdc_generate_wrapper_function
sp_cdc_generate_wrapper_function
dev_langs
TSQL

sys.sp_cdc_generate_wrapper_function (Transact-SQL)

[!INCLUDE SQL Server]

Generates scripts to create wrapper functions for the change data capture query functions that are available in [!INCLUDE ssNoVersion]. The API supported in the generated wrappers enables specifying the query interval as a datetime interval. This specification makes the function good for use in many warehousing applications, including those applications developed by [!INCLUDE ssISnoversion] package designers who are using change data capture technology to determine incremental load.

For more information on the functions generated by sys.sp_cdc_generate_wrapper_function, see sys.fn_all_changes_<capture_instance> and sys.fn_net_changes_<capture_instance>.

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

Syntax

sys.sp_cdc_generate_wrapper_function
    [ [ @capture_instance sysname = ] 'capture_instance' ]
    [ , [ @closed_high_end_point = ] closed_high_end_pt ]
    [ , [ @column_list = ] N'column_list' ]
    [ , [ @update_flag_list = ] N'update_flag_list' ]
[ ; ]

Arguments

[ @capture_instance = ] 'capture_instance'

The capture instance that scripts are generated for @capture_instance is sysname and has a default value of NULL. If a value is omitted or explicitly set to NULL, wrapper scripts are generated for all capture instances.

[ @closed_high_end_point = ] high_end_pt_flag

The flag bit that indicates whether changes that have a commit time equal to the high endpoint are included within the extraction interval by the generated procedure. @closed_high_end_point is bit and has a default value of 1, which indicates that the endpoint should be included. A value of 0 indicates that all commit times are strictly less than the high endpoint.

[ @column_list = ] N'column_list'

A list of captured columns included in the result set that is returned by the wrapper function. @column_list is nvarchar(max) and has a default value of NULL. When NULL is specified, all captured columns are included.

[ @update_flag_list = ] N'update_flag_list'

A list of included columns for which an update flag is included in the result set returned by the wrapper function. @update_flag_list is nvarchar(max) and has a default value of NULL. When NULL is specified, no update flags are included.

Return code values

0 (success) or 1 (failure).

Result set

Column name Column type Description
function_name nvarchar(145) Name of the generated function.
create_script nvarchar(max) The script that creates the capture-instance wrapper function.

Remarks

The script that creates the function to wrap the all-changes query for a capture instance is always generated. If the capture instance supports net-changes queries, the script to generate a wrapper for this query is also generated.

Examples

The following example show how you can use sys.sp_cdc_generate_wrapper_function to create wrappers for all the change data capture functions.

DECLARE @wrapper_functions TABLE (
    function_name SYSNAME,
    create_script NVARCHAR (MAX));

INSERT INTO @wrapper_functions
EXECUTE sys.sp_cdc_generate_wrapper_function;

DECLARE @create_script AS NVARCHAR (MAX);

DECLARE #hfunctions CURSOR LOCAL FAST_FORWARD
    FOR SELECT create_script
        FROM @wrapper_functions;

OPEN #hfunctions;

FETCH #hfunctions INTO @create_script;

WHILE (@@fetch_status <> -1)
    BEGIN
        EXECUTE sp_executesql @create_script;
        FETCH #hfunctions INTO @create_script;
    END

CLOSE #hfunctions;

DEALLOCATE #hfunctions;

Related content