-- =============================================
-- Description:  a dbo sémában lévő nem globális tárolt eljárások 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].[uspCreateSchemaStoredProcedures]
GO

CREATE PROCEDURE [dev].[uspCreateSchemaStoredProcedures]
  @pSchemaName NVARCHAR(100), -- pl: 'KR_BEDO_Schema' | NULL esetén az összes sémán végigmegy
  @pStoredProcedureNames XML  -- pl: '<StoredProcedureNames><StoredProcedureName>sp_GetOrarend</StoredProcedureName></StoredProcedureNames>' | NULL esetén az összes nem globális tárolt eljáráson végigmegy
AS
BEGIN

  DECLARE @objectId INT
  DECLARE @storedProcedureName NVARCHAR(4000)
  DECLARE @storedProcedureDefinition NVARCHAR(MAX)
  DECLARE @schemaName NVARCHAR(100)
  DECLARE @deleteStoredProcedureSQL NVARCHAR(4000)
  DECLARE @createStoredProcedureSQL 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 @storedProcedures CURSOR
  IF @pStoredProcedureNames IS NOT NULL 
    SET @storedProcedures = CURSOR LOCAL FOR
      SELECT DISTINCT p.object_id, p.name, m.Definition
      FROM @pStoredProcedureNames.nodes('StoredProcedureNames/StoredProcedureName') AS StoredProcedureNames(StoredProcedureName) 
        INNER JOIN sys.procedures p ON p.name = StoredProcedureName.value('(.)[1]', 'varchar(100)')
        INNER JOIN sys.sql_modules m ON p.object_id = m.object_id
        INNER JOIN sys.schemas s ON s.schema_id = p.schema_id
      WHERE 
        s.name ='dbo'
  ELSE
    SET @storedProcedures = CURSOR LOCAL FOR
      SELECT p.object_id, p.name, m.Definition
      FROM sys.procedures p
        INNER JOIN sys.sql_modules m ON p.object_id = m.object_id
        INNER JOIN sys.schemas s ON s.schema_id = p.schema_id
      WHERE s.name ='dbo' AND p.name NOT LIKE 'sp_Global_%'
      ORDER BY p.name

  OPEN @storedProcedures
  FETCH NEXT FROM @storedProcedures INTO @objectId, @storedProcedureName, @storedProcedureDefinition

  WHILE @@FETCH_STATUS = 0 BEGIN
    IF OBJECT_ID(@objectId) IS NULL BEGIN
      SET @storedProcedureDefinition = REPLACE(@storedProcedureDefinition, 'dbo.fn','#schemaName#.fn')   
      SET @storedProcedureDefinition = REPLACE(@storedProcedureDefinition, 'dbo.[fn','#schemaName#.[fn')   
      SET @storedProcedureDefinition = REPLACE(@storedProcedureDefinition, '[dbo].fn','#schemaName#.fn')   
      SET @storedProcedureDefinition = REPLACE(@storedProcedureDefinition, '[dbo].[fn','#schemaName#.[fn')   
      SET @storedProcedureDefinition = REPLACE(@storedProcedureDefinition, 'dbo.','')   
      SET @storedProcedureDefinition = REPLACE(@storedProcedureDefinition, '[dbo].','')      
  
      OPEN @schemaNames
      FETCH NEXT FROM @schemaNames INTO @schemaName
    
      WHILE @@FETCH_STATUS = 0 BEGIN

        SET @createStoredProcedureSQL = REPLACE(@storedProcedureDefinition, 'CREATE PROCEDURE ','CREATE OR ALTER PROCEDURE [' + @schemaName + '].')
        SET @createStoredProcedureSQL = REPLACE(@createStoredProcedureSQL, '#schemaName#.fn','[' + @schemaName + '].fn')      
        SET @createStoredProcedureSQL = REPLACE(@createStoredProcedureSQL, '#schemaName#.[fn','[' + @schemaName + '].[fn')      
      
        PRINT CAST(@objectId AS VARCHAR(10)) + '  ' + @storedProcedureName + '   ' + @schemaName

        EXEC sp_executesql @createStoredProcedureSQL
    
        FETCH NEXT FROM @schemaNames INTO @schemaName
      END

      CLOSE @schemaNames
    END

    FETCH NEXT FROM @storedProcedures INTO @objectId, @storedProcedureName, @storedProcedureDefinition
  END             

  CLOSE @storedProcedures
  DEALLOCATE @storedProcedures
  DEALLOCATE @schemaNames

END

GO