-- =============================================
-- Author:		Paolo Giovanetti
-- Create date: 17 marzo 2009
-- Description:	SP FAKE per popolare le tabelle host del SIMPB in modo da avere qualche dato in più
--				quando si eseguono le SP del Presentation Layer
-- =============================================
CREATE procedure [C6StagingPeriodico].[SRV_CREA_QUERY_STORICO]
AS
BEGIN
	DECLARE @SCHEMA AS VARCHAR(50)
	DECLARE @SCHEMASTORICO AS VARCHAR(50)
	DECLARE @TABLE AS VARCHAR(50)
	DECLARE @CAMPI VARCHAR(MAX)
	DECLARE @CONDITION AS VARCHAR(50)
	SET @SCHEMA = 'C6StagingPeriodico'
	SET @SCHEMASTORICO = 'C6StoricoPeriodico'
	DECLARE tabelle CURSOR FOR
	SELECT UPPER(TABLE_NAME)
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_TYPE = 'BASE TABLE'
		AND TABLE_SCHEMA = @SCHEMA 
	ORDER BY TABLE_SCHEMA, TABLE_NAME
	OPEN tabelle
	FETCH NEXT FROM tabelle
	INTO @TABLE
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @CAMPI = ''
		SELECT @CAMPI = @CAMPI + ', ' + COLUMN_NAME 
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = @TABLE
			AND TABLE_SCHEMA = @SCHEMA 
		SET @CAMPI = RIGHT(@CAMPI, LEN(@CAMPI) -2 ) 
		SET @CONDITION = ' WHERE ID_ELAB = @ID_ELAB'
		IF CHARINDEX('ID_ELAB', @CAMPI) = 0 -- CAMPO ID_ELAB NON PRESENTE
		BEGIN 
			SET @CAMPI = @CAMPI + ', @ID_ELAB'
			SET @CONDITION = ''
		END
		PRINT	'	--XXXXXXXXXXXXXXXXXXXXXXXX ' + @TABLE + ' XXXXXXXXXXXXXXXXXXXXXXXX--'
		PRINT	'	IF(@NomeTabella IS NULL OR @NomeTabella = ''' + @TABLE + ''')'
		PRINT	'	BEGIN'
		PRINT	'		DELETE FROM ' + @SCHEMASTORICO + '.ST_' + @TABLE + ' WHERE ID_ELAB >= @ID_ELAB OR ID_ELAB < @MIN_ID_ELAB'
		PRINT	'		INSERT INTO ' + @SCHEMASTORICO + '.ST_' + @TABLE 
		PRINT	'		(' + REPLACE(@CAMPI,'@','') + ')'
		PRINT	'		SELECT '
		PRINT	'		 ' + @CAMPI
		PRINT	'		FROM ' + @SCHEMA + '.' + @TABLE + @CONDITION
		PRINT	'	END'
		PRINT	''
		FETCH NEXT FROM tabelle
		INTO @TABLE
	END
	CLOSE tabelle
	DEALLOCATE tabelle
END