sp_MSupd_table - serious repl help needed

Last Post 31 Jan 2006 10:11 AM by RSP_SQL. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
SQL_Jr
New Member
New Member

--
31 Jan 2006 07:44 AM
Need Serious Replication HELP!

I have a situation where I have synched up the data bet publisher and subscriber manually, and then put in a custom sp_MSupd_table stored proc which is called when there is an update. However upon synch, I get the following error failure: Cannot insert the value NULL into column 'ColID', table 'DB.dbo.MyTable'; column does not allow nulls. UPDATE fails.

Here is my SP: (I've changed the name references to protect the innocent.
Profiler shows its passing NULLS. If one can help me understand how the values get to the SP parameters during REPL. Or, tell me what I am missing. Do I need CASE WHEN statements? But if COL=NULL what value would be passed?? TIA

CREATE procedure sp_MSupd_Table
@ColID int,
@C2ID int,
@C3 char(1),
@C5 varchar(2000),
@C5 int,
@pkc1 int
,@bitmap binary(3)
as

If exists ( select 1 from Table where ColID = @pkc1)
Begin
update Table
set ColID=@ColID,
C2=@C2,
C3=@C3,
C4=@C4,
C5=@C5
where ColID=@pkc1
End
Else
Begin
Insert Table(ColID,C2,C3,C4,C5)
Values (@ColID,@C2,@C3,@C5)
End
GO
RSP_SQL
New Member
New Member

--
31 Jan 2006 10:11 AM
Assuming you don't want it to allow NULL values, you have nothing in the SP to evaluate IF THERE IS NULL THEN.....
Here's your code below, modified for CASE WHEN So, if the parameters are NULL the SP will evaluate that and simple keep the existing value in table........Hope this helps!


CREATE procedure sp_MSupd_Table
@ColID int,
@C2ID int,
@C3 char(1),
@C5 varchar(2000),
@C5 int,
@pkc1 int
,@bitmap binary(3)
as

If exists ( select 1 from Table where ColID = @pkc1)
Begin
update Table
set ColID= Case When @ColID IS NULL THEN ColID ELSE @ColID END,
C2= Case When @C2 IS NULL THEN C2 ELSE @C2 END,
C3= Case When @C3 IS NULL THEN C3 ELSE @C3 END,
C4= Case When @C4 IS NULL THEN C4 ELSE @C4 END,
C5= Case When @C5 IS NULL THEN C5 ELSE @C5 END
where ColID=@pkc1
End
Else
Begin
Insert Table(ColID,C2,C3,C4,C5)
Values (@ColID,@C2,@C3,@C5)
End
You are not authorized to post a reply.

Acceptable Use Policy