Ad:

MS SQL Discussion Thread
Unsolved
Views: 5287

Dec 2nd, 2008

Add or Update stored procedure

Expand Post »

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

SQL Syntax (Toggle Plain Text)
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
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

Last edited by peter_budo; Dec 3rd, 2008 at 7:16 pm. Reason: Correcting closing tag from [code] to [/code]

Similar Threads

Login and retrieve user data from database in ASP.NET
XML Update?? in RSS, Web Services and SOAP
Update a column in SQL using C# in C#
ASP.Net VB Page to update a users profile not updating. in MS SQL
Long ado net question but urgent help required in ASP.NET

eddy556

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

32 posts
since Jan 2008

Permalink

Dec 3rd, 2008

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

Traicey

Reputation Points: 26

Solved Threads: 19

Posting Whiz in Training

Offline

283 posts
since Mar 2008

Permalink

Dec 3rd, 2008

Re: Add or Update stored procedure

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

eddy556

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

32 posts
since Jan 2008

Permalink

Dec 3rd, 2008

Re: Add or Update stored procedure

Well on that case you need to have an else statement

sql Syntax (Toggle Plain Text)
IF @rc IS NULL
   BEGIN
  --SET @rc = 0
    INSERT INTO Method VALUES (@Method, @Mdescript)
         SELECT @@IDENTITY
    END
    ELSE
       UPDATE YOUR RECORD
       SELECT @RC
IF @rc IS NULL
   BEGIN
  --SET @rc = 0
    INSERT INTO Method VALUES (@Method, @Mdescript)
         SELECT @@IDENTITY
    END
    ELSE
       UPDATE YOUR RECORD
       SELECT @RC

Traicey

Reputation Points: 26

Solved Threads: 19

Posting Whiz in Training

Offline

283 posts
since Mar 2008

Permalink

Dec 3rd, 2008

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

eddy556

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

32 posts
since Jan 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Error In trigger.

Next Thread in MS SQL Forum Timeline: Overflow full database to a new database

DaniWeb Message

Cancel Changes

User Name
Password