CREATE proc [dbo].[KB_UpdateArticle](@id int, @header varchar(255), @txt text, @active varchar(1),
@allowcomment varchar(1), @extracontent text, @sortorder int, @metakeywords varchar(255), @metadescription varchar(512), @publishdate datetime )
as
If @id = -1
BEGIN
insert into kb_article (header, txt, created, lastmodified, viewcount,votecount,
totalrate, active, allowcomment, allowrating, extracontent, sortorder, metakeywords , metadescription, publishdate )
select @header, @txt, getdate(), getdate(), 0,0,
0, @active, @allowcomment, '', @extracontent, @sortorder, @metakeywords , @metadescription, @publishdate
select @@identity
END
ELSE
BEGIN
update kb_article set header=@header, txt=@txt, lastmodified=getdate(),
active=@active, allowcomment=@allowcomment, extracontent=@extracontent,
sortorder=@sortorder, @metakeywords =metakeywords , @metadescription=metadescription, @publishdate=publishdate
where id=@id
select @id
END
GO
Spot the error? It is an "authentic" stored procedure from a CMS system I am developing - and the lesson to learn is to always test out all test scenarios - even after the smallest additions. I added some new columns in the table, metakeywords and metadescription, and had to update the stored procedure to update it accordingly.
And in the "update" case
@metakeywords =metakeywords , @metadescription=metadescription, @publishdate=publishdate
should be
metakeywords = @metakeywords, metadescription= @metadescription, publishdate= @publishdate
of course. My biggest error was not this, but not testing that case out. I just created a NEW article and saw that the information made it into the database - then I was happy with it. I can tell you I had some trouble finding the cause later, at first I looked in the code of course.