
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GenerateNumber]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:  (C) Vitacard System 2009
-- Create date: 2009-08-05
-- Description: Генерирует номер
-- =============================================
CREATE PROCEDURE [dbo].[sp_GenerateNumber]
 @EntityID int,
 @NumberName varchar(64),
 @count int = 1  
AS 
BEGIN 
 declare @trancount int 
 declare @NextNumber int 

 declare @msg as varchar(64) 

 set @NextNumber = null 

 set nocount on  
 select @trancount = @@trancount 
 if @trancount = 0 begin tran 
  
 select @NextNumber = NextNumber from T_GEN_NUMBERS with(rowlock,xlock) where EntityID = @EntityID and NumberName like @NumberName 
  
 if @NextNumber is null 
  begin
   set @NextNumber = 1
   insert into T_GEN_NUMBERS (GenNumbersID, EntityID, NumberName, NextNumber) values (newid(), @EntityID, @NumberName, @NextNumber+@count-1)
   if @@error <> 0 goto err1
  end
 else
  begin 
   set @NextNumber = @NextNumber + 1
   update T_GEN_NUMBERS set NextNumber = @NextNumber+@count-1 where EntityID = @EntityID and NumberName = @NumberName 
   if @@error <> 0 goto err1 
  end 

 if @trancount = 0 commit tran 
 return @NextNumber
  
 err1: 
 if @trancount = 0 rollback tran 
 set @msg = ''Ошибка генерации номера.'' 
 raiserror (@msg, 73000,1) 
 return -1
END' 
END
GO


