Add or Update stored procedure
 

The following stored procedure is supposed to add a new record to the database OR update Mdescript if it already exists. However I keep getting errors such as primary key violations. Please take a look, many thanks :)

USE [COSHH2008]

GO

/****** Object:  StoredProcedure [dbo].[CHSP_OR_ACTIVITYAREA_001]    Script Date: 12/02/2008 13:59:33 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[CHSP_OU_METHOD_001] @Method VARCHAR(50), @Mdescript VARCHAR(MAX) 

                                                                        

AS



DECLARE @rc integer



set @rc =



(        SELECT @@ROWCOUNT

        FROM  Method as a  

        --

                WHERE a.Method = @Method AND a.Mdescript = @Mdescript)



IF        @rc IS NULL

        BEGIN



        --SET @rc = 0



        INSERT INTO Method VALUES (@Method, @Mdescript)

        

END



DECLARE @mc integer



SET @mc =



(SELECT @@ROWCOUNT



FROM Method as m



WHERE m.METHOD = @Method)



IF @mc = 1

        BEGIN

        

        UPDATE Method

        SET Mdescript = @Mdescript

        WHERE Method = @Method



        



END

Re: Add or Update stored procedure
 

Primary key violation usually means that U are trying to enter or insert a value that already exist in the primary key field, there is nothing wrong I see in the code, Primary keys have to be unique

Re: Add or Update stored procedure
 

Yes I know that, hence if it already exists it should do an update! Not a replace.

Re: Add or Update stored procedure
 

Well on that case you need to have an else statement

IF @rc IS NULL

   BEGIN

  --SET @rc = 0

    INSERT INTO Method VALUES (@Method, @Mdescript)

         SELECT @@IDENTITY

    END

    ELSE

       UPDATE YOUR RECORD

       SELECT @RC

Re: Add or Update stored procedure
 

Hi thanks for that, but it turned out the whole of the code can be simplified using IF EXISTS, arghh this is so obvious when I think about it. Sorry about the newbie post everyone