CREATE PROCEDURE dbo.GetAcctNextPaymentXML
(
	@key nvarchar(20),
	@value1 int output,
	@value int output,
	@MyError nvarchar(100) output
)
AS

SELECT @value1 = AccValue
FROM AcctKeys
WHERE AccKey = @key

begin transaction
UPDATE AcctKeys
SET AccValue = AccValue + 1
WHERE (AccKey = @Key)

if @@error <> 0
begin
	SELECT @MyError = @@error
end
SELECT @value = AccValue
FROM AcctKeys
WHERE AccKey = @key
commit transaction

quote:

---

Originally posted by tkizer

Why aren't you just using the reliable Identity option?

In order to get yours to work, you'd need to put a lock on the AcctKeys table to ensure no other process is accessing the same data.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

---

quote:

---

Originally posted by Michael Valentine Jones

If you use an IDENTITY column in your table, it will increment automatically when you insert a new row. It is more efficient, scalable, and dependable than your solution, so it is almost always a better way to do this.

You should read about IDENTITY in SQL Server Books Online.





CODO ERGO SUM

---

CREATE PROCEDURE dbo.GetAcctNextPaymentXML
(
	@key nvarchar(20),
	@value1 int output,
	@value int output,
	@MyError nvarchar(100) output
)
AS

declare @error int, @rowcount int, @New_value int

UPDATE AcctKeys
SET
	@New_value = AccValue = AccValue + 1
WHERE
	AccKey = @Key

select @error = @@error, @rowcount = @@rowcount

if @error <> 0
	begin
	set @MyError = @error
	end
else
	if @rowcount = 1 and @New_value is not null
		begin
		set @value  = @New_value
		set @value1 = @value-1
		end
	else
		begin
		set @MyError = -9999
		end

quote:

---

Originally posted by Iain

Michael

Thanks, but it seems like the only difference in how you have changed the sp, is in what happens when @@error is anything else but 0, what i failed to mention earlier is that @@error is not throwing anything but 0 so the else part of your if statement will never get hit. Please correct if i have mis-understood.

Cheers

Iain

---