Stored Procedure Help Please! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure Help Please!

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-02-21 : 11:03:51

I have the following stored procedure:


Create procedure sp_updateCostBreakdown

@account varchar(10),
@product varchar(20),
@cost float,
@result int [OUTPUT]

AS

update CostBreakdown set Cost = @cost
where Account = @account
and Product = @product

set @output = @@error

if @output = 0
insert into CostBreakdown (Account, Product, Cost)
values (@account, @product, @cost)

set @output = @@error
return;

When I call this from my application, it works fine but I don't understand the output I am getting back.

I find that if the record does not exist, it adds the new one and the output = 0. If the record does exist, the update is successful but the return code is 2627. Where can I find out the possible values returned by @@error.

Mike B

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-02-21 : 11:46:15

Mike, I don't see you assigning a value to @result anywhere in your procedure. How can you see the return code of 2627? Also running an update on a non-existent row will not produce an error, it simply updates no rows. This should work for your scenario:


IF EXISTS (SELECT 1 FROM CostBreakdown WHERE Account = @account AND Product = @product)
BEGIN
  UPDATE CostBreakdown set Cost = @cost
  WHERE Account = @account
  AND Product = @product
END

ELSE
BEGIN
  INSERT INTO CostBreakdown (Account, Product, Cost)
  VALUES (@account, @product, @cost)
END

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-02-21 : 11:48:45

Alternatively, your original proc could work with a slight modification:


Create procedure sp_updateCostBreakdown

@account varchar(10),
@product varchar(20),
@cost float,
@result int [OUTPUT]

AS

update CostBreakdown set Cost = @cost
where Account = @account
and Product = @product

set @output = @@rowcount

if @output = 0
insert into CostBreakdown (Account, Product, Cost)
values (@account, @product, @cost)

set @output = @@error
return

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-02-21 : 12:19:15

quote:
Originally posted by mohdowais

Mike, I don't see you assigning a value to @result anywhere in your procedure. How can you see the return code of 2627? Also running an update on a non-existent row will not produce an error, it simply updates no rows. This should work for your scenario:

IF EXISTS (SELECT 1 FROM CostBreakdown WHERE Account = @account AND Product = @product)
BEGIN
UPDATE CostBreakdown set Cost = @cost
WHERE Account = @account
AND Product = @product
END

ELSE
BEGIN
INSERT INTO CostBreakdown (Account, Product, Cost)
VALUES (@account, @product, @cost)
END

OS

Sorry, I cannot believe I did that, @result and @output are the same variable. In the actual stored proc it is @output. I must have type result without noticing. Sometimes type what I think at the moment. :)

Mike B

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2004-02-21 : 22:11:05

2627 is a primary key violation. Because you expected an error from an "unupdated update" but didn't get one, @Output was always 0 and it would always attempt to insert, causing a primary key violation because it attempted to insert duplicate data.

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources