Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_scriptupdateparams

  No additional text.


Syntax
create procedure sys.sp_scriptupdateparams
(
    @src_objid int
    ,@artid int
    ,@artcolcounter bit = 0 -- 0 means it does column counting base on pk, 1 means base on article columns
    ,@param_count int = NULL output
    ,@publishertype tinyint=1        -- 1 = mssqlserver, 2 = heterogeneous
    ,@userdefinedtypestobasetypes bit=0
    ,@xmltontext bit=0
    ,@maxtypestomatchingnonmaxtypes bit=0
    ,@maptimestamp bit = 1
    ,@setdefaultnulls bit = 0
    ,@newdatetimetypestostrings bit = 0
    ,@hierarchyidtovarbinarymax bit = 0
    ,@largeuserdefinedtypestovarbinarymax bit = 0
    ,@spatialtypestovarbinarymax bit = 0
)
as
begin
    declare @this_col int
                ,@art_col int
                ,@spacer nvarchar(10)
                ,@isset int
                ,@isvalidcolumn tinyint
                ,@cmd nvarchar(4000)
                ,@typestring nvarchar(255)
                ,@pubtypemssqlserver tinyint
                ,@pubtypeheterogeneous tinyint

    -- add colval parameters
    select @param_count = NULL
            ,@art_col = 1
            ,@spacer = N' '
            ,@cmd = ''
            ,@pubtypemssqlserver = 1
            ,@pubtypeheterogeneous = 2

    if (@publishertype not in (@pubtypemssqlserver, @pubtypeheterogeneous))
    begin
        raiserror(21402, 16, 4, '@publishertype')
        return 1
    end

    if (@publishertype = @pubtypemssqlserver)
    begin
        declare hCColid cursor local fast_forward for
            select column_id from sys.columns where object_id = @src_objid order by column_id asc
    end
    else
    begin
        declare hCColid cursor local fast_forward for
            select colid from IHsyscolumns where id = @src_objid order by colorder asc
    end
    OPEN hCColid
    FETCH hCColid INTO @this_col
    WHILE ( 1 = 1 )
    begin
        if @@fetch_status = -1
        begin
            -- If called by sp_scriptxupdproc and it is the first time
            -- at the end of the cursor loop
            if @artcolcounter = 1 and @param_count is NULL
            begin
                -- Reset it so that we know we encountered cursor end once.
                select @param_count = 0
                -- Reopen cursor
                CLOSE hCColid
                DEALLOCATE hCColid
                if (@publishertype = @pubtypemssqlserver)
                begin
                    declare hCColid cursor local fast_forward for
                        select column_id from sys.columns where object_id = @src_objid order by column_id asc
                end
                else
                begin
                    declare hCColid cursor local fast_forward for
                        select colid from IHsyscolumns where id = @src_objid order by colorder asc
                end
                OPEN hCColid
                FETCH hCColid INTO @this_col
                continue
            end
            else
                break;
        end
        exec @isset = sys.sp_isarticlecolbitset @this_col, @artid, @publishertype
        if (@publishertype = @pubtypemssqlserver)
            select @isvalidcolumn = case when exists (select name from sys.columns where object_id=@src_objid and @this_col=column_id and is_computed<>1) then 1 else 0 end
        else
            select @isvalidcolumn = case when exists (select name from IHsyscolumns where id=@src_objid and @this_col=colid ) then 1 else 0 end
        if @isset = 1 and @isvalidcolumn = 1
        begin
            select @typestring = sys.fn_gettypestring (@src_objid, @this_col, @publishertype, @userdefinedtypestobasetypes, @xmltontext, @maxtypestomatchingnonmaxtypes, @maptimestamp, @newdatetimetypestostrings, @hierarchyidtovarbinarymax, @largeuserdefin
edtypestovarbinarymax, @spatialtypestovarbinarymax)
            select @cmd = @cmd + @spacer + N'@c' + convert( nvarchar, @art_col ) + N' ' + @typestring
            select @art_col = @art_col + 1

            if @setdefaultnulls = 1
            begin
				select @cmd = @cmd + ' = null'
            end

            select @spacer = N','

            if len( @cmd ) > 3000
            begin
            insert into #proctext(procedure_text) values( @cmd )
                select @cmd = N''
            end
        end
        FETCH hCColid INTO @this_col
    end
    CLOSE hCColid
    DEALLOCATE hCColid

    select @param_count = @art_col -1

    -- add pkval parameters
    -- If it is 1 we are called by sp_scriptxupdproc, no need for PK params
    if @artcolcounter = 0
    begin
        select @art_col = 1
        if (@publishertype = @pubtypemssqlserver)
        begin
            declare hCColid cursor local fast_forward for
                select column_id from sys.columns where object_id = @src_objid order by column_id asc
        end
        else
        begin
            declare hCColid cursor local fast_forward for
                select colid from IHsyscolumns where id = @src_objid order by colorder asc
        end
        OPEN hCColid
        FETCH hCColid INTO @this_col
        WHILE (@@fetch_status <> -1)
        begin
            exec @isset = sys.sp_MSiscolpk @src_objid, @this_col, @publishertype
            if (@publishertype = @pubtypemssqlserver)
                select @isvalidcolumn = case when exists (select name from sys.columns where object_id=@src_objid and @this_col=column_id) then 1 else 0 end
            else
                select @isvalidcolumn = case when exists (select name from IHsyscolumns where id=@src_objid and @this_col=colid ) then 1 else 0 end
            if @isset = 1 and @isvalidcolumn = 1
            begin
                select @typestring = sys.fn_gettypestring (@src_objid, @this_col, @publishertype, @userdefinedtypestobasetypes, @xmltontext, @maxtypestomatchingnonmaxtypes, @maptimestamp, @newdatetimetypestostrings, @hierarchyidtovarbinarymax, @largeuser
definedtypestovarbinarymax, @spatialtypestovarbinarymax)
                select @cmd = @cmd + @spacer + N'@pkc' + convert( nvarchar, @art_col ) + N' ' + @typestring
                select @art_col = @art_col + 1
                select @spacer = N','

                if len( @cmd ) > 3000
                begin
                insert into #proctext(procedure_text) values( @cmd )
                    select @cmd = N''
                end
            end
            FETCH hCColid INTO @this_col
        end
        CLOSE hCColid
        DEALLOCATE hCColid
    end

    insert into #proctext(procedure_text) values ( @cmd )
    return 0
end

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_scriptupdproccore (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