-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CreateSubscriber]
    @Name nchar(50),
    @ClientId int,
    @Msisdn nchar(12) = null,
    @TrplId int,
    @Comment nchar(100) = Null,
    @CreDate datetime = Null,
    @SubscriberId int OUTPUT
AS
BEGIN
    declare @getid CURSOR;
    declare @servId int;
    declare @msg varchar(256);

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    if @CreDate is NULL
        Set @CreDate = GETDATE();

    Set @SubscriberId = (SELECT MAX(SUBS_ID) FROM SUBSCRIBER) + 1;
    if @SubscriberId is NULL
        Set @SubscriberId = 1;

    -- по умолчанию, абонент бует создаваться со статусом = 4 (подготовлен, т.е. не активированным)
    insert into SUBSCRIBER(SUBS_ID, SUBS_STAT_ID, NAME, CLNT_ID, MSISDN, TRPL_ID, COMMENT, CRE_DATE)
            values(@SubscriberId, 4, @Name, @ClientId, @Msisdn, @TrplId, @Comment, @CreDate);

    -- добавляем абоненту все услуги с тарифного плана со статусом = 4 (подготовлен, т.е. не активированным)
    SET @getid = CURSOR FOR
      Select SERV_ID from TARIFF_SERVICE where TRPL_ID = @TrplId

    open @getid
    FETCH NEXT
    FROM @getid INTO @servId
    WHILE @@FETCH_STATUS = 0
    BEGIN
        declare @id int;
        execute AddServiceToSubscriber @SubsId = @SubscriberId, @ServId = @servId, @ServStatId = 4, @SubsServId = @id output , @ErrMsg = @msg output
        FETCH NEXT
        FROM @getid INTO @servId
    END
    CLOSE @getid
    DEALLOCATE @getid
END
go