﻿DECLARE SchemaCursor CURSOR FOR 
SELECT DISTINCT 
	i.C_AZONOSITO AS IntezmenyAzonosito
	,i.ID AS IntezmenyId
	,t2.ID AS TanevId
FROM T_TANEV t
	INNER JOIN T_INTEZMENY i ON i.ID = t.C_INTEZMENYID
	INNER JOIN T_TANEV t2 ON t2.C_INTEZMENYID = t.C_INTEZMENYID AND t2.TOROLT = 'F' AND t2.C_AKTIV = 'T'
WHERE t.TOROLT = 'F' AND i.TOROLT = 'F'

DECLARE 
	@schemaName nvarchar(100)
	,@intezmenyAzonosito nvarchar(100)
	,@intezmenyId int
	,@tanevID int
	,@sql nvarchar(max)
  
OPEN SchemaCursor
FETCH NEXT FROM SchemaCursor INTO @intezmenyAzonosito, @intezmenyId, @tanevID

WHILE @@FETCH_STATUS = 0 BEGIN
	SET @sql = ''
	SET @schemaName = 'KR_' + @intezmenyAzonosito + '_Schema'

	IF OBJECT_ID('[' + @schemaName + '].[T_ERTMONDATBANKTIPUS]') IS NOT NULL 
	BEGIN
		SELECT @sql += 'DROP VIEW [' + @schemaName + '].T_ERTMONDATBANKTIPUS' + ';' + char(13) + char(10)
		SELECT @sql += 'DROP VIEW [' + @schemaName + '].T_ERTMONDATBANKTIPUS_OSSZES'+ ';' + char(13) + char(10)
	END  
	EXEC sp_executesql @sql

	SET @sql = ''
	SELECT @sql += 'CREATE VIEW [' + @schemaName + '].T_ERTMONDATBANKTIPUS as select * from dbo.T_ERTMONDATBANKTIPUS where C_ALINTEZMENYID = ' + cast(@intezmenyId as nvarchar(50)) + ' and C_ALTANEVID = ' + cast(@tanevID as nvarchar(50)) + char(13) + char(10)
	EXEC sp_executesql @sql

	SET @sql = ''
	SELECT @sql += 'CREATE VIEW [' + @schemaName + '].T_ERTMONDATBANKTIPUS_OSSZES as select * from dbo.T_ERTMONDATBANKTIPUS where C_ALINTEZMENYID = ' + cast(@intezmenyId as nvarchar(50)) + char(13) + char(10)
	EXEC sp_executesql @sql
	
	FETCH NEXT FROM SchemaCursor INTO @intezmenyAzonosito, @intezmenyId, @tanevID
END

CLOSE SchemaCursor
DEALLOCATE SchemaCursor
GO