sp_scriptupdproc (T-SQL)

Generates the CREATE PROCEDURE statement to create a custom stored procedure.

Syntax

sp_scriptupdproc [@artid =] artid

Arguments

[@artid =] artid

Is the article ID. artid is int, with no default.

Result Sets

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.

Remarks

sp_scriptupdproc is used in transactional replication.

Permissions

Members of the public role can execute sp_scriptupdproc.

Examples

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.

'CALL sp_upd_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_scriptupdproc @artid = 1

The statement returns:

CREATE PROCEDURE [sp_upd_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)

AS

IF @c1 = @pkc1

UPDATE [authors] SET [au_lname] = @c2,[au_fname] = @c3,
    [phone] = @c4,[address] = @c5,[city] = @c6,[state] = @c7,
    [zip] = @c8,[contract] = @c9

WHERE [au_id] = @pkc1

ELSE

UPDATE [authors] SET [au_id] = @c1,[au_lname] = @c2,
    [au_fname] = @c3,[phone] = @c4,[address] = @c5,
    [city] = @c6,[state] = @c7,[zip] = @c8,[contract] = @c9

WHERE [au_id] = @pkc1

This stored procedure can be used to generate custom stored procedures for manually synchronized articles or for articles for which the automatic generation of custom stored procedures has been disabled.

See Also