MINOVA DEMO

??ALTER PROCEDURE [dbo].[PRPTAIDATA] 

(

	@SelectionID VARCHAR(20)

	,@Year VARCHAR(20)

	,@Month VARCHAR(20)

)

AS

--DECLARE @SelectionID VARCHAR(20) = '0001'

--DECLARE @Year VARCHAR(20) = ''

--DECLARE @Month VARCHAR(20) = ''

DECLARE @TblRefAI VARCHAR(200)

SELECT @TblRefAI = TableRefAI FROM AI_SELECTIONS

WHERE Selection_ID = @SelectionID

DECLARE @TblField TABLE ( FieldName VARCHAR(250), SeqNum DECIMAL(18,0))

INSERT INTO @TblField

SELECT FieldName, SeqNum FROM dbo.SDATATABLEFIELD WHERE TableName = @TblRefAI ORDER BY SeqNum

DECLARE @StrAI VARCHAR(MAX)

DECLARE @TblDt TABLE

(

	SeqID DECIMAL(18,0)

	,Year VARCHAR(200)

	,Month VARCHAR(200)

	,SeqNum DECIMAL(18,0)

	,FieldName VARCHAR(200)

	,IsiField VARCHAR(MAX)

)

DECLARE @SeqID DECIMAL(18,0) = 0

DECLARE @Seq VARCHAR(250)

DECLARE @c_FieldName VARCHAR(250)

DECLARE @c_SeqNum VARCHAR(250)

DECLARE cur_tbl CURSOR FOR SELECT FieldName, SeqNum FROM @TblField WHERE FieldName NOT IN ('Year', 'Month')

OPEN cur_tbl

FETCH cur_tbl INTO @c_FieldName ,@c_SeqNum

WHILE @@Fetch_Status = 0

	BEGIN

		SET @SeqID = @SeqID + 1

		SET @StrAI = 'SELECT ROW_NUMBER() OVER(ORDER BY Year,Month ASC) AS SeqID, Year, '

					+ ' dbo.fn_formatdatetime((Year+Month+''01''),''mmmm'') AS Month, '

					+ '''' + @c_SeqNum + ''' AS SeqNum, '

					+ '''' + @c_FieldName + ''' AS FieldName, [' + @c_FieldName + '] FROM ' + @TblRefAI 

					+ ' WHERE (Year = ''' + @Year + ''' OR ''' + @Year + ''' = '''')'

					+ ' AND (Month = ''' + @Month + ''' OR ''' + @Month + ''' = '''')'

		--SELECT @StrAI

		INSERT INTO @TblDt

		EXEC(@StrAI)	

	FETCH cur_tbl INTO @c_FieldName ,@c_SeqNum  

	END

CLOSE cur_tbl

DEALLOCATE cur_tbl

SELECT * FROM @TblDt