-- Name:
-- sp_IHscriptupdateparams
-- Description:
-- Script update with params
-- Security:
-- Public (for use by snapshot agent)
-- Returns:
-- Success or failure
-- Temp table (#proctext) of commands
-- Owner:
--
create procedure sys.sp_IHscriptupdateparams
(
@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
)
as
begin
declare @this_col int
declare @art_col int
declare @spacer nvarchar(10)
declare @isset int
declare @cmd nvarchar(4000)
-- add colval parameters
select @param_count = -1
select @art_col = 1
select @spacer = N' '
select @cmd = ''
DECLARE hCColid CURSOR LOCAL FAST_FORWARD FOR
select colid from IHsyscolumns where id = @src_objid order by colorder asc
OPEN hCColid
FETCH hCColid INTO @this_col
WHILE ( 1 = 1 )
begin
if @@fetch_status = -1
begin
-- If called by sp_IHscriptxupdproc and it is the first time
-- at the end of the cursor loop
if @artcolcounter = 1 and @param_count = -1
begin
-- Reset it so that we know we encountered cursor end once.
select @param_count = 0
-- Reopen cursor
CLOSE hCColid
DEALLOCATE hCColid
DECLARE hCColid CURSOR LOCAL FAST_FORWARD FOR
select colid from IHsyscolumns where id = @src_objid order by colorder asc
OPEN hCColid
FETCH hCColid INTO @this_col
continue
end
else
break;
end
exec @isset = sys.sp_IHisarticlecolbitset @this_col, @artid
if @isset != 0 and EXISTS (select name from IHsyscolumns where id=@src_objid and @this_col=colid)
begin
select @cmd = @cmd + @spacer + N'@c' + convert( nvarchar, @art_col ) + N' ' + sys.fn_IHgettypestring(@src_objid, @this_col)
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
select @param_count = @art_col -1
-- add pkval parameters
-- If it is 1 we are called by sp_IHscriptxupdproc, no need for PK params
if @artcolcounter = 0
begin
select @art_col = 1
DECLARE hCColid CURSOR LOCAL FAST_FORWARD FOR
select ihc.publishercolumn_id
from IHsyscolumns ihsc,
IHcolumns ihc
where ihc.column_id = ihsc.colid
and ihsc.id = @src_objid
order by ihsc.colorder asc
OPEN hCColid
FETCH hCColid INTO @this_col
WHILE (@@fetch_status <> -1)
begin
exec @isset = sys.fn_IHiscolpk @this_col
if @isset != 0 and EXISTS (select name from IHsyscolumns where id=@src_objid and @this_col=colid )
begin
select @cmd = @cmd + @spacer + N'@pkc' + convert( nvarchar, @art_col ) + N' ' + sys.fn_IHgettypestring(@src_objid, @this_col)
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 )
end