Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_cdc_extract_column

  No additional text.


Syntax
create procedure [sys].[sp_cdc_extract_column]
(
	@column sysname output,
	@list nvarchar(max) output
)	
as
begin
	declare	@quote_char nchar
		,@retcode int
		,@comma nchar
		,@double_quote nchar
		,@left_bracket nchar
		,@right_bracket nchar
		,@right_quote nchar
		,@left_index int
		,@right_index int
		,@comma_index int
		,@list_length int
		,@column_length int
		,@two_quotes nchar(2)
		
    set nocount on

    set @column = null
    set @comma = N','
    set @double_quote = N'"'
    set @left_bracket = N'['
    set @right_bracket = N']'
    set @right_quote = null
    set @list_length = len(@list)

    -- Determine the quote character for the list, if any.
	-- If the first non-whitespace character of the list is '[' or '"'
	-- use it as the quote character.  Otherwise, assume no quote character.
	set @quote_char = substring(sys.fn_cdc_trim_whitespace(@list), 1, 1)
	if ((@quote_char != N'[') and (@quote_char != N'"'))
	begin
		set @quote_char = null
	end

    if (@quote_char = @double_quote)
    begin
		set @right_quote = @double_quote
	end
	
	if (@quote_char = @left_bracket)
    begin
		set @right_quote = @right_bracket
	end
	
	set @two_quotes  = @right_quote + @right_quote
	
	if (@quote_char is null)
	begin
		-- Locate index of next appearance of comma
		set @comma_index = charindex(@comma, @list, 1)
		
		-- If there are no remaining commas in the list, set the
		-- comma index to the end of the string
		if (@comma_index is null) or (@comma_index = 0)
		begin
			set @column_length = @list_length
		end
		else	
		begin
			set @column_length = @comma_index - 1
		end

		-- Set @column to characters between the beginning of the list and the comma
		set @column = substring(@list, 1, @column_length)
		
		-- Remove leading and trailing whitespace from column name
		set @column = sys.fn_cdc_trim_whitespace(@column)
		
	end
	else
	begin	
		-- Locate index of next appearance of left quote
		set @left_index = charindex(@quote_char, @list, 1)

		-- Locate index of next appearance of right quote
		set @right_index = [sys].[fn_cdc_next_right_quote](@right_quote, @list, @left_index+1)
		if (@right_index = 0)
		begin
			raiserror(22965, 16, -1)
			return 1
		end	

		-- Locate index of next appearance of comma
		set @comma_index = charindex(@comma, @list, @right_index+1)
		
		-- Set @column to characters spanned by left and right quote characters
		-- not including the quote characters
   		set @column_length = (@right_index - @left_index) - 1
		set @column = substring(@list, @left_index+1, @column_length)
		
		-- Replace any embedded quote characters that were escaped with single quote characters
		set @column = replace(@column, @two_quotes, @right_quote)
    end

	if (@comma_index is null) or (@comma_index = 0)
	begin
		set @list = null
	end
	else
	begin
		set @list_length = @list_length - @comma_index
		set @list = substring(@list, @comma_index+1, @list_length)
	end	

    -- Verify column name extracted is a valid system identifier
    exec @retcode = [sys].[sp_validname] @column

    if (@retcode != 0)
    begin
		return 1
	end
		
	return 0
end

 
Last revision 2008RTM
See also

  sp_cdc_parse_captured_column_list (Procedure)
sp_cdc_parse_included_column_list (Procedure)
sp_cdc_parse_update_flag_list (Procedure)
sp_MSdrop_cdc (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash