create procedure sys.sp_MSscript_params (
@objid int,
@artid int,
@postfix nvarchar(8) = NULL,
@bOutParams tinyint = 0, -- boolean indicating wether or not to declare timestamp/identity params as output params
@outvars nvarchar(4000) = NULL output
,@agent_id int = NULL -- null @agent_id means this is executed at pub side,
-- pub queries sysarticlecolumns for article column partition,
-- sub uses MSsubscription_articlecolumns
)
as
BEGIN
set nocount on
declare @cmd nvarchar(4000)
,@colname sysname
,@typestring nvarchar(4000)
,@spacer nvarchar(1)
,@spacer2 nvarchar(1)
,@this_col int
,@art_col int
,@isset int
,@ispkcol int
select @spacer = N' '
,@spacer2 = N' '
,@art_col = 1
,@outvars = null
,@cmd = N' '
DECLARE #hccolid CURSOR LOCAL FAST_FORWARD FOR
select column_id from sys.columns where object_id = @objid order by column_id asc
OPEN #hccolid
FETCH #hccolid INTO @this_col
WHILE (@@fetch_status <> -1)
begin
if @artid is not null
exec @isset = sys.sp_isarticlecolbitset @this_col, @artid, 1, @agent_id
else
select @isset = 1
exec @ispkcol = sys.sp_MSiscolpk @objid, @this_col
if @isset != 0 and EXISTS (select name from sys.columns where object_id=@objid and column_id=@this_col and (is_computed<>1 or @ispkcol<>0))
begin
exec sys.sp_MSget_type @objid, @this_col, @colname output, @typestring OUTPUT
if (@typestring IS NOT NULL)
begin
-- for xml type - we will use nvarchar(max)
if (@typestring = 'xml')
select @typestring = 'nvarchar(max)'
if @postfix is null
select @cmd = @cmd + @spacer + N'@c' + convert(varchar(4), @this_col) + N' ' + @typestring
else
-- old vars
select @cmd = @cmd + @spacer + N'@c' + convert(varchar(4), @this_col) + @postfix + N' ' + @typestring
-- new vars of type timestamp and identity are declared as output params
if @bOutParams = 1 and (@typestring = N'timestamp' or ColumnProperty(@objid, @colname, 'IsIdentity') = 1)
begin
select @cmd = @cmd + N' output'
select @outvars = isnull(@outvars, N'') + @spacer2 + N'@c' + convert(varchar(4), @this_col) + N' = ' + quotename(@colname)
select @spacer2 = N','
end
select @spacer = N','
end
select @art_col = @art_col + 1
-- flush command if necessary
exec sys.sp_MSflush_command @cmd output, 1, 4
end
FETCH #hccolid INTO @this_col
end
CLOSE #hccolid
DEALLOCATE #hccolid
-- all done
return 0
END