declare @BankCheckNumbers table (BankID int, NextCheckNumber varchar(35))

insert @BankCheckNumbers
select 1, '40124' union all
select 2, '3211' union all
select 3, '3241' union all
select 4, '5211' union all
select 5, '8811' 

declare @CheckRegister table (CheckID int, BankID int, CheckNumber varchar(35))

insert @CheckRegister
select 1, 1, NULL union all
select 2, 1, NULL union all
select 3, 2, NULL union all
select 4, 5, NULL union all
select 4, 5, NULL union all
select 4, 4, NULL union all
select 4, 3, NULL

Select * From @BankCheckNumbers
Select * From @CheckRegister

	UPDATE BankCheckNumbers WITH(ROWLOCK) 
		SET @Value = NextCheckNumber = NextCheckNumber + 1
	Where BankCheckNumbers.BankID= @BankAccountSysID

Return @NextNumber

quote:

---

Originally posted by mhall

My colleague suggested that I grab the next ID in a Tran so as to lock views to the BankCheckNumbers, increment the number outside of the table, then update BankCheckNumbers with the last number incremented inside the transaction. My gut tells me that this is a bad idea, but given enough time I might be able to rationalize this over making 1000 requests from the app. Would that be crazy?

Thanks,
Mark

---

quote:

---

Originally posted by mhall

Thanks for the suggestion. I can't do an identity column because I have multiple bank accounts with different check sequences and now that I think about it, I wouldn't be able to do what my colleague suggested either because I wouldn't just be grabbing one sequence run. In the case of the above table I would need a new check number from all 5 of the master accounts.
Damn, hate to do this at the client.

Cheers,
Mark

---

Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.

Create FUNCTION [dbo].[IW_BankGetNextCheckNumber]
(
	@BankAccountSysID int
)
RETURNS varchar(40)
AS
Begin
	DECLARE @Value Int

	UPDATE dbo.IW_BankAccount WITH(ROWLOCK) 
		SET @Value = NextCheckNumber = NextCheckNumber + 1
	Where dbo.IW_BankAccount.BankAccountSysID = @BankAccountSysID

	Return cast(@Value as varchar(40))
End

quote:

---

Originally posted by mhall

That would be awesome, but to my knowledge, you can't update inside of a user-defined function. When I tried to it threw this error

Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.

If you know of a way to do that I would be much oblidged if you would show me how.

Cheers,
Mark

---