| | |
Add or Update stored procedure
Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
![]() |
•
•
Join Date: Jan 2008
Posts: 32
Reputation:
Solved Threads: 0
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
Last edited by peter_budo; Dec 3rd, 2008 at 7:16 pm. Reason: Correcting closing tag from [code] to [/code]
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
Some people get so rich they lose all respect for humanity. That's how rich I want to be.
![]() |
Similar Threads
- XML Update?? (RSS, Web Services and SOAP)
- Update a column in SQL using C# (C#)
- ASP.Net VB Page to update a users profile not updating. (MS SQL)
- Login and retrieve user data from database (ASP.NET)
- Long ado net question but urgent help required (ASP.NET)
- ADO.NET question modification. (VB.NET)
- Deleting a Row From A DataGrid (VB.NET)
- STORED PROCEDURE in .NET please help... (Oracle)
Other Threads in the MS SQL Forum
- Previous Thread: Error In trigger.
- Next Thread: Overflow full database to a new database
| Thread Tools | Search this Thread |





