DROP PROCEDURE IF EXISTS dev.uspCreateSchemaSPFN
GO

CREATE PROCEDURE dev.uspCreateSchemaSPFN
   @pRoutinList nvarchar(max) -- Vesszővel elválasztott SP és FN lista (akár vegyesen is), NULL esetén az összeset frissíti
  ,@pSchemaName nvarchar(max) = NULL -- Schema neve, amit frissíteni kell, NULL esetén az összes schemán végigmegy
AS
BEGIN
  SET NOCOUNT ON
  
  DECLARE 
     @spList nvarchar(max)
    ,@fnList nvarchar(max)
    ,@notExistingObject nvarchar(max)
    ,@dbName nvarchar(80)

  DECLARE @Routine TABLE (
     Id int IDENTITY(1, 1)
    ,RoutineName nvarchar(255)
  )

  IF @pRoutinList IS NULL BEGIN
    EXEC dev.uspCreateSchemaFunctions @pSchemaName, NULL
    EXEC dev.uspCreateSchemaStoredProcedures @pSchemaName, NULL
    
    RETURN
  END

  INSERT INTO @Routine (RoutineName)
  SELECT LTRIM(RTRIM(s.value))
  FROM STRING_SPLIT (@pRoutinList, N',') s

  SET @notExistingObject = NULL
    SELECT @notExistingObject = ISNULL(@notExistingObject + ', ', '') + r.RoutineName  
    FROM @Routine r 
    WHERE NOT EXISTS (
      SELECT 1 FROM sys.all_objects p 
      WHERE r.RoutineName COLLATE HUNGARIAN_CI_AS = p.name COLLATE HUNGARIAN_CI_AS 
        AND p.schema_id = 1 
        AND p.type IN ('P', 'FN', 'IF', 'TF') )

    IF @notExistingObject IS NOT NULL BEGIN
      SET @dbName = DB_NAME()
      RAISERROR('A parameterekben megadott SP/FN-ek kozul a kovettkezok nem leteznek a %s adatbazisban: %s', 16, 1, @dbName, @notExistingObject)
      RETURN
    END

  SET @fnList = (
    SELECT r.ROUTINE_NAME AS "FunctionName" 
    FROM INFORMATION_SCHEMA.ROUTINES r
      INNER JOIN @Routine s ON RoutineName = r.ROUTINE_NAME
    WHERE r.ROUTINE_SCHEMA = 'dbo'
      AND r.ROUTINE_TYPE = 'FUNCTION'
    ORDER BY s.Id
    FOR XML PATH(''), ROOT('FunctionNames')
  )
  
  SET @spList = (      
    SELECT r.ROUTINE_NAME AS "StoredProcedureName" 
    FROM INFORMATION_SCHEMA.ROUTINES r
      INNER JOIN @Routine s ON RoutineName = r.ROUTINE_NAME
    WHERE r.ROUTINE_SCHEMA = 'dbo'
      AND r.ROUTINE_TYPE = 'PROCEDURE'
    ORDER BY s.Id
    FOR XML PATH(''), ROOT('StoredProcedureNames')
  )

  IF @fnList IS NOT NULL BEGIN  
    EXEC dev.uspCreateSchemaFunctions @pSchemaName, @fnList
  END  
  IF @spList IS NOT NULL BEGIN
    EXEC dev.uspCreateSchemaStoredProcedures @pSchemaName, @spList
  END
END
GO