45 lines
1.6 KiB
Transact-SQL
45 lines
1.6 KiB
Transact-SQL
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 |