"MikeB" <m@nospam.com> wrote in message
news:urOLiVIrHHA.4020@TK2MSFTNGP05.phx.gbl...
>I have a table called e_next_number that holds a variety of different IDs
> currently in use. The columns are NumberType (nchar) and ID (int). My
> question is, what is the best way to insure a inquire number is returned,
> currently my stored procedure looks like this:
>
> CREATE PROCEDURE _GetNextNumber
> @NumberType nchar(10),
> @NextNumber int OUTPUT
> AS
>
> SELECT @NextNumber = ID + 1 FROM e_next_number WHERE numbertype =
> @NumberType
> UPDATE e_next_number SET ID = @NextNumber WHERE numbertype = @NumberType
>
> return
>
> TIA
>

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:eAVDyPJrHHA.3492@TK2MSFTNGP02.phx.gbl...
> Add a transaction and some hints:
>
> CREATE PROCEDURE _GetNextNumber
> @NumberType nchar(10),
> @NextNumber int OUTPUT
> AS
>
> SET NOCOUNT ON
>
> BEGIN TRAN
>
> SELECT @NextNumber = ID + 1
> FROM e_next_number WITH (HOLDLOCK,XLOCK)
> WHERE numbertype = @NumberType
>
> UPDATE e_next_number SET ID = @NextNumber WHERE numbertype = @NumberType
>
> IF @@TRANCOUNT > 0
> COMMIT TRAN
>
> return
>
> --
> Andrew J. Kelly SQL MVP
>
> "MikeB" <m@nospam.com> wrote in message
> news:urOLiVIrHHA.4020@TK2MSFTNGP05.phx.gbl...
>>I have a table called e_next_number that holds a variety of different IDs
>> currently in use. The columns are NumberType (nchar) and ID (int). My
>> question is, what is the best way to insure a inquire number is returned,
>> currently my stored procedure looks like this:
>>
>> CREATE PROCEDURE _GetNextNumber
>> @NumberType nchar(10),
>> @NextNumber int OUTPUT
>> AS
>>
>> SELECT @NextNumber = ID + 1 FROM e_next_number WHERE numbertype =
>> @NumberType
>> UPDATE e_next_number SET ID = @NextNumber WHERE numbertype = @NumberType
>>
>> return
>>
>> TIA
>>
>
>