DROP PROCEDURE IF EXISTS dev.[uspCreateSchemaAndOthers]
GO

CREATE PROCEDURE dev.[uspCreateSchemaAndOthers]
	 @pDbJelszo nvarchar(50)
	,@pIntezmenyIds nvarchar(500) = NULL
AS
BEGIN
	SET NOCOUNT ON;

  DECLARE @intCursor CURSOR

  DECLARE
     @intezmenyId int
    ,@tanevId int
    ,@intezmenyAzonosito nvarchar(80)
    ,@schemaName nvarchar(80)
  
  IF @pIntezmenyIds IS NULL BEGIN
    SET @intCursor = CURSOR FOR
    SELECT i.ID, tv.ID, i.C_AZONOSITO
    FROM T_INTEZMENY i
      INNER JOIN T_TANEV tv ON tv.C_INTEZMENYID = i.ID AND tv.TOROLT = 'F' AND tv.C_AKTIV = 'T'
    WHERE i.TOROLT = 'F'
  END
  ELSE BEGIN
    SET @intCursor = CURSOR FOR
    SELECT i.ID, tv.ID, i.C_AZONOSITO
    FROM T_INTEZMENY i
      INNER JOIN STRING_SPLIT(@pIntezmenyIds, ',') ss ON LTRIM(RTRIM(ss.value)) = i.ID
      INNER JOIN T_TANEV tv ON tv.C_INTEZMENYID = i.ID AND tv.TOROLT = 'F' AND tv.C_AKTIV = 'T'
    WHERE i.TOROLT = 'F'
  END

  OPEN @intCursor
  FETCH NEXT FROM @intCursor INTO @intezmenyId, @tanevId, @intezmenyAzonosito
      
  WHILE @@FETCH_STATUS = 0 BEGIN


    SET @schemaName = 'KR_' + @intezmenyAzonosito + '_Schema'

    EXEC dev.uspCreateSchema
       @pIntezmenyAzonosito = @intezmenyAzonosito
      ,@pSchemaUserPassword = @pDbJelszo;

 	  EXEC dev.uspCreateSchemaViews
 	     @pTables = NULL
 	    ,@pSchemas =  @schemaName
 	    ,@pDebugMode = 0

    EXEC dev.uspCreateSchemaSPFN
       @pRoutinList = NULL
      ,@pSchemaName = @schemaName
        
    FETCH NEXT FROM @intCursor INTO @intezmenyId, @tanevId, @intezmenyAzonosito
    
  END
  
  CLOSE @intCursor
  DEALLOCATE @intCursor
  
END
GO