Rodney,

You realize that the SP can return incorrect values to the caller? Someone else can have updated the value between the time the update and the select runs.

Using code like this will increment the value and return it without the possibility of conflict.

update Entities
set
  @DblSpare1 = DblSpare1 = IsNull(DblSpare1, 0) + 1

return @DblSpare1

>The stored proc is as follows:
>
>create procedure [dbo]._bspNextAuditNo
>with encryption
>as
>
>set nocount on
>
>update Entities
>set
> DblSpare1 = IsNull(DblSpare1, 0) + 1
>
>declare @NextAuditNo int
>set @NextAuditNo = (select top 1 DblSpare1 from Entities)
>return @NextAuditNo