Stored Procedures with Insert Clauses

The insert procedures are identical at the primary and replicate sites. The upper-level stored procedure that controls the insert procedures and the insert procedures observe the following logic:

• A publisher record is inserted only when there is no title ID.

• A title record is inserted only when the publisher exists.

-- Execute this script at NY and SF data servers
-- Creates stored procedure 
 create procedure ins_publishers
 (@pub_id char(4), @pub_name varchar(40)=null,
 city varchar(20)=null, @state char(2)=null)
 as
 	insert publishers values (@pub_id,
 	@pub_name, @city, @state)
 /* end of script */

-- Execute this script at NY and SF data servers
 -- Creates stored procedure 
 create procedure ins_titles
 (@title_id tid, @title varchar(80), @type char(12),
 @pub_id char(4)=null, @price money=null, @advance money=null,
 @total_sales int=null, @notes varchar(200)=null,
 @pubdate datetime, @contract bit)
 as
 if not exists (select pub_id from publishers
 		where pub_id=@pub_id)
 	raiserror 20001 “** FATAL ERROR: Invalid publishers id **”
 else
 	insert titles values (@title_id, @title, @type, @pub_id,
 	@price,	@advance, @total_sales, @notes, @pubdate, @contract)
 /* end of script */

-- Execute this script at NY and SF data servers
 -- Creates stored procedure 
 create procedure ins_pub_title
 (@pub_id char(4), @pub_name varchar(40)=null,
 @city varchar(20)=null, @state char(2),
 @title_id tid=null, @title varchar(80)=null, @type char(12)=null,
 @price money=null, @advance money=null,
 @total_sales int=null, @notes varchar(200)=null,
 @pubdate datetime=null, @contract bit)
 as
 begin
 	if @pub_name != null
 	exec ins_publishers @pub_id, @pub_name, @city, @state
 	if @title_id != null
 	exec ins_titles @title_id, @title, @type, @pub_id, @price,
 	@advance, @total_sales, @notes, @pubdate, @contract
 end/* 
 end of script */