DROP PROCEDURE IF EXISTS dev.uspDropSchemaSPFN
GO

CREATE PROCEDURE dev.uspDropSchemaSPFN
   @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 
     @sql nvarchar(max) = '';

  IF @pSchemaName IS NULL AND @pRoutinList IS NULL BEGIN
    RETURN
  END

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

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


  IF @pSchemaName IS NULL BEGIN

    SELECT @sql += 'DROP ' + IIF(o.type = 'P', 'PROCEDURE', 'FUNCTION') + ' IF EXISTS [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + ']' + char(13) + char(10) 
    FROM sys.objects o
      INNER JOIN @Routine r ON r.RoutineName COLLATE HUNGARIAN_CI_AS = o.name COLLATE HUNGARIAN_CI_AS
    WHERE o.type IN ('P', 'FN', 'IF', 'TF')
    ORDER BY r.Id
  END

  IF @pRoutinList IS NULL BEGIN

    SELECT @sql += 'DROP ' + IIF(o.type = 'P', 'PROCEDURE', 'FUNCTION') + ' IF EXISTS [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + ']' + char(13) + char(10) 
    FROM sys.objects o
    WHERE o.type IN ('P', 'FN', 'IF', 'TF')
      AND SCHEMA_NAME(o.schema_id) = @pSchemaName
    

  END  

  IF  @pRoutinList IS NULL AND @pSchemaName IS NOT NULL BEGIN

    SELECT @sql += 'DROP ' + IIF(o.type = 'P', 'PROCEDURE', 'FUNCTION') + ' IF EXISTS [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + ']' + char(13) + char(10) 
    FROM sys.objects o
      INNER JOIN @Routine r ON r.RoutineName COLLATE HUNGARIAN_CI_AS = o.name COLLATE HUNGARIAN_CI_AS
    WHERE o.type IN ('P', 'FN', 'IF', 'TF')
      AND SCHEMA_NAME(o.schema_id) = @pSchemaName
    ORDER BY r.Id

  END

  EXEC sp_executesql @sql

END
GO