create procedure sys.sp_MSscript_endproc (
@objid int
,@op_type varchar(3) = 'ins' -- 'ins', 'upd', 'del'
,@artid int
,@outvars nvarchar(4000)
,@queued_pub bit = 0
,@identity_insert bit = 0
)
as
BEGIN
declare @cmd nvarchar(4000)
declare @qualname nvarchar(517)
exec sp_MSget_qualified_name @objid, @qualname OUTPUT
-- start scripting
select @cmd = N'
' + N'
' + N'-- decide the return code
' + N'
if (@execution_mode = @immediate)
begin
if @error != 0
return -1
-- Return special code to indicate the subscriber row needs to be
-- refreshed.
if @rowcount = 0
return 5
end'
insert into #proctext(procedure_text) values(@cmd)
-- operation specific stuff
if (@queued_pub = 1)
begin
if (@op_type = 'ins')
begin
select @cmd = N'
if (@execution_mode = @QFirstPass)
begin
if (@rowcount = 0)
begin
if (@error in (547, 2601, 2627))
return 2 -- insert conflict
else
return -1 -- error
end
end'
end
else if (@op_type = 'upd')
begin
select @cmd = N'
if (@execution_mode = @QFirstPass)
begin
if (@rowcount = 0)
begin
if (@error in (0, 547, 2601, 2627))
return 1 -- update conflict
else
return -1 -- error
end
end'
end
else if (@op_type = 'del')
begin
select @cmd = N'
if (@execution_mode = @QFirstPass)
begin
if (@rowcount = 0)
begin
if (@error in (0, 547))
return 3 -- delete conflict
else
return -1 -- error
end
end'
end
insert into #proctext(procedure_text) values(@cmd)
-- continue with scripting
select @cmd = N'
if (@execution_mode in (@QPubWins, @QSubWins))
begin
if (@@error != 0 or @retcode != 0'
-- identity insert specific scripting
if (@identity_insert = 1)
select @cmd = @cmd + N' or @iderror != 0'
-- continue with scripting
select @cmd = @cmd + N')
return -1 -- error
end
'
insert into #proctext(procedure_text) values(@cmd)
end
-- if we have output vars to assign do it now
if (@outvars is not null)
begin
if @op_type = 'upd'
begin
-- Script out pk var assigment that used in sp_MSscript_where_clause
exec sp_MSscript_pkvar_assignment @objid, @artid, 1, null, null, null, @queued_pub
insert into #proctext(procedure_text) values(N'
')
end
select @cmd = N'
select ' + @outvars + N'
from ' + @qualname
insert into #proctext(procedure_text) values( @cmd)
insert into #proctext(procedure_text) values( N'
')
if (@op_type = 'ins')
exec sp_MSscript_where_clause @objid, @artid, 'new pk', null, 4
else if (@op_type = 'upd')
exec sp_MSscript_where_clause @objid, @artid, 'old pk', null, 4
end
-- Final part of the proc
select @cmd = N'
' + N'
' + N'-- past all checks
' + N'
return 0
END
'
insert into #proctext(procedure_text) values(@cmd)
-- all done
return 0
END