/*
Тест множественной раскатки
Основная процедура
*/
CREATE OR ALTER  PROCEDURE dbo.Raskatka_main
@number INT
WITH EXECUTE AS OWNER
AS

	--------------------------------------
	/*DECLARE
    @LoadSchema_int  TABLE
	(
	[Schema]    varchar(50)      NOT NULL,
  [Name]      VARCHAR (100)      NOT NULL,
  [NewSchema] VARCHAR(100)      NULL,
  [NewName]   VARCHAR(100)      NULL,
  [Params]    nvarchar(4000) NULL
  	)*/
    DECLARE
	@Schema         sysname = OBJECT_SCHEMA_NAME(@@PROCID),
    @Name           sysname = OBJECT_NAME(@@PROCID),
    @NewSchema      sysname,
    @NewName        sysname,
    @NewProcedure   NVARCHAR(400),
    @LoadParams     NVARCHAR(4000);

    --INSERT INTO @LoadSchema_int([Schema], [Name], [NewSchema], [NewName], [Params])
    SELECT TOP 1  @Schema=[Schema], @Name= [Name], @NewSchema = [NewSchema], @NewName = [NewName], @LoadParams= [Params]
    FROM [Load].[Schema@Find^2]( @Schema ,@Name ,null,  CAST(@number AS VARCHAR(50)))
	WHERE CHARINDEX( cast (@number AS VARCHAR(50)),[Params])>0;




    IF (@NewSchema IS NOT NULL) AND (@NewSchema <> @Schema OR @NewName <> @Name)
      BEGIN
		SET @NewProcedure = quotename(@NewSchema) + N'.' + quotename(@NewName);
		SELECT 'Новая процедура ',quotename(@NewSchema) + N'.' + quotename(@NewName);
	  END
	ELSE
		SELECT 'Обычная процедура ',quotename(@NewSchema) + N'.' + quotename(@NewName);

	  --- тесты процедуры
	  /*
	UPDATE load.[Schemas^2]
	SET scope = '[{"Schema": "dbo", "Name": "Raskatka_main", "NewSchema": "dbo", "NewName": "Raskatka_1", "Params":{"ShopNo":["123"]}},{"Schema": "dbo", "Name": "Raskatka_main", "NewSchema": "dbo", "NewName": "Raskatka_2", "Params":{"ShopNo":["345"]}}]' , [Values] = '123,345'
	WHERE [Name] = 'Raskatka_main'

	EXEC raskatka_main @number = 123
	EXEC dbo.Raskatka_main @number = 345
	EXEC raskatka_main @number = 456

	  */