-- =============================================
-- Description:  a dbo sémában lévő függvények frissítése az intézményi sémákba
-- =============================================

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DROP PROCEDURE IF EXISTS [dev].[uspCreateSchemaFunctions]
GO

CREATE PROCEDURE [dev].[uspCreateSchemaFunctions]
  @pSchemaName NVARCHAR(100), -- pl: 'KR_BEDO_Schema' | NULL esetén az összes sémán végigmegy
  @pFunctionNames XML         -- pl: '<FunctionNames><FunctionName>fnGetHetNapja</FunctionName></FunctionNames>' | NULL esetén az összes függvényen végigmegy
AS
BEGIN

  DECLARE @objectId INT
  DECLARE @functionName NVARCHAR(4000)
  DECLARE @functionDefinition NVARCHAR(MAX)
  DECLARE @schemaName NVARCHAR(100)
  DECLARE @deleteFunctionSQL NVARCHAR(4000)
  DECLARE @createFunctionSQL NVARCHAR(MAX)

  DECLARE @schemaNames CURSOR
  IF @pSchemaName IS NOT NULL 
    SET @schemaNames = CURSOR LOCAL FOR
      SELECT @pSchemaName
  ELSE
    SET @schemaNames = CURSOR LOCAL FOR
      SELECT name FROM sys.schemas s
      INNER join T_INTEZMENY i ON 'KR_' + i.C_AZONOSITO + '_schema' = s.name 
      WHERE principal_id = 1
        AND i.TOROLT = 'F' 
      ORDER BY name

  DECLARE @functions CURSOR
  IF @pFunctionNames IS NOT NULL 
    SET @functions = CURSOR LOCAL FOR
      SELECT DISTINCT f.object_id, f.name, m.definition
      FROM @pFunctionNames.nodes('FunctionNames/FunctionName') AS FunctionNames(FunctionName)
        INNER JOIN sys.all_objects f ON f.name = FunctionName.value('(.)[1]', 'varchar(100)')
        INNER JOIN sys.sql_modules m ON f.object_id = m.object_id
        INNER JOIN sys.schemas s ON s.schema_id = f.schema_id
      WHERE 
        s.name ='dbo' AND f.type IN ('FN', 'AF', 'FS', 'FT', 'IF', 'TF')
  ELSE
    SET @functions = CURSOR LOCAL FOR
      SELECT f.object_id, f.name, m.definition
      FROM sys.all_objects f
        INNER JOIN sys.sql_modules m ON f.object_id = m.object_id
        INNER JOIN sys.schemas s ON s.schema_id = f.schema_id
      WHERE s.name ='dbo' AND f.type IN ('FN', 'AF', 'FS', 'FT', 'IF', 'TF')
      ORDER BY f.name

  OPEN @functions 
  FETCH NEXT FROM @functions INTO @objectId, @functionName, @functionDefinition

  WHILE @@FETCH_STATUS = 0 BEGIN
    IF OBJECT_ID(@objectId) IS NULL BEGIN
      SET @functionDefinition = REPLACE(@functionDefinition, 'CREATE FUNCTION dbo.fn','CREATE FUNCTION fn')
      SET @functionDefinition = REPLACE(@functionDefinition, 'CREATE FUNCTION [dbo].fn','CREATE FUNCTION fn')
      SET @functionDefinition = REPLACE(@functionDefinition, 'dbo.fn','#schemaName#.fn')
      SET @functionDefinition = REPLACE(@functionDefinition, '[dbo].fn','#schemaName#.fn')
      SET @functionDefinition = REPLACE(@functionDefinition, 'dbo.','')
      SET @functionDefinition = REPLACE(@functionDefinition, '[dbo].','')

      OPEN @schemaNames
      FETCH NEXT FROM @schemaNames INTO @schemaName
    
      WHILE @@FETCH_STATUS = 0 BEGIN
        SET @deleteFunctionSQL = 'IF OBJECT_ID(''[' + @schemaName + '].' +  @functionName + ''') IS NOT NULL
          DROP FUNCTION [' + @schemaName + '].' + @functionName

        SET @createFunctionSQL = REPLACE(@functionDefinition, 'CREATE FUNCTION ','CREATE FUNCTION [' + @schemaName + '].')
        SET @createFunctionSQL = REPLACE(@createFunctionSQL, '#schemaName#.fn','[' + @schemaName + '].fn')      

        PRINT CAST(@objectId AS VARCHAR(10)) + '  ' + @functionName + '   ' + @schemaName
          
        EXEC sp_executesql @deleteFunctionSQL
        EXEC sp_executesql @createFunctionSQL
    
        FETCH NEXT FROM @schemaNames INTO @schemaName
      END

      CLOSE @schemaNames
    END

    FETCH NEXT FROM @functions INTO @objectId, @functionName, @functionDefinition
  END             

  CLOSE @functions
  DEALLOCATE @functions
  DEALLOCATE @schemaNames

END

GO