[RESOLVED] help with a sproc (update/insert)
CREATE PROCEDURE [dbo].[SaveUser]
@ID int,
@UserName varchar(50),
@Password varchar(50),
@EmailAddress varchar(50)
AS
BEGIN
IF EXISTS (SELECT * FROM [user] WHERE [id] = @ID)
BEGIN
UPDATE [user]
SET [username] = @UserName,
[password] = @Password,
[email_address] = @EmailAddress
WHERE [id] = @ID
END
ELSE
BEGIN
INSERT INTO [user] ([username], [password], [email_address]) VALUES (@UserName, @Password, @EmailAddress)
END
END
and exec'ing it with the following:
use [dev]
GO
EXEC [dbo].[SaveUser]
$ID = 32, -- random number that doesnt exist in the db
$Username = N'asdf',
$Password = N'asdf',
$EmailAddress = N'asdf'
GO
returns the following error message:
Msg 2627, Level 14, State 1, Procedure SaveUser, Line 18
Violation of PRIMARY KEY constraint 'PK_user'. Cannot insert duplicate key in object 'dbo.user'.
Here's the table. there are no constraints, no triggers, no FK's and other indexes besides the PK_user, and there is only one record in the table w/ an ID of 0
USE [dev]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[user](
[id] [int] NOT NULL CONSTRAINT [DF_user_id] DEFAULT ((0)),
[username] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[password] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[email_address] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_user] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

