Generates the CREATE PROCEDURE statement to create a custom stored procedure.
sp_scriptmappedupdproc [@artid =] artid
Returns a result set that consists of a single nvarchar(4000) column. The result set forms the complete CREATE PROCEDURE statement necessary to create the custom stored procedure.
sp_scriptmappedupdproc is used in transactional replication.
Only members of the sysadmin fixed server role or the db_owner fixed database role can execute sp_scriptmappedupdproc.
This example creates an article (with artid set to 1) on the authors table in the pubs database and specifies the UPDATE statement to be the custom procedure to execute.
'MCALL sp_mupd_authors'
The custom stored procedures to be executed by the Distribution Agent at the Subscriber are generated by running this stored procedure at the Publisher.
EXEC sp_scriptmappedupdproc @artid = '1'
The statement returns:
CREATE PROCEDURE [sp_mupd_authors]
@c1 varchar(11),@c2 varchar(40),@c3 varchar(20),@c4 char(12),
@c5 varchar(40), @c6 varchar(20),@c7 char(2),@c8 char(5),@c9 bit,
@pkc1 varchar(11),@bitmap binary(2)
AS
IF substring(@bitmap,1,1) & 1 = 1
UPDATE [authors] SET
[au_id] = CASE substring(@bitmap,1,1) & 1 WHEN 1 THEN @c1
ELSE [au_id] END
,[au_lname] = CASE substring(@bitmap,1,1) & 2 WHEN 2 THEN @c2
ELSE [au_lname] END
,[au_fname] = CASE substring(@bitmap,1,1) & 4 WHEN 4 THEN @c3
ELSE [au_fname] END
,[phone] = CASE substring(@bitmap,1,1) & 8 WHEN 8 THEN @c4
ELSE [phone] END
,[address] = CASE substring(@bitmap,1,1) & 16 WHEN 16 THEN @c5
ELSE [address] END
,[city] = CASE substring(@bitmap,1,1) & 32 WHEN 32 THEN @c6
ELSE [city] END
,[state] = CASE substring(@bitmap,1,1) & 64 WHEN 64 THEN @c7
ELSE [state] END
,[zip] = CASE substring(@bitmap,1,1) & 128 WHEN 128 THEN @c8
ELSE [zip] END
,[contract] = CASE substring(@bitmap,2,1) & 1 WHEN 1 THEN @c9
ELSE [contract] END
WHERE [au_id] = @pkc1
ELSE
UPDATE [authors] SET
[au_lname] = CASE substring(@bitmap,1,1) & 2 WHEN 2 THEN @c2
ELSE [au_lname] END
,[au_fname] = CASE substring(@bitmap,1,1) & 4 WHEN 4 THEN @c3
ELSE [au_fname] END
,[phone] = CASE substring(@bitmap,1,1) & 8 WHEN 8 THEN @c4
ELSE [phone] END
,[address] = CASE substring(@bitmap,1,1) & 16 WHEN 16 THEN @c5
ELSE [address] END
,[city] = CASE substring(@bitmap,1,1) & 32 WHEN 32 THEN @c6
ELSE [city] END
,[state] = CASE substring(@bitmap,1,1) & 64 WHEN 64 THEN @c7
ELSE [state] END
,[zip] = CASE substring(@bitmap,1,1) & 128 WHEN 128 THEN @c8
ELSE [zip] END
,[contract] = CASE substring(@bitmap,2,1) & 1 WHEN 1 THEN @c9
ELSE [contract] END
where [au_id] = @pkc1
After running this stored procedure, you can use the produced script to manually create the procedure at the Subscribers.
| sp_scriptdelproc | sp_scriptinsproc |
| sp_scriptupdproc | System Stored Procedures |