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
