BEGIN

	DECLARE @TABLE_NAME varchar(100), @SQL nvarchar(MAX)
	DECLARE @cols nvarchar(max), @colname nvarchar(255), @delname nvarchar(255)

	SELECT @TABLE_NAME = ObjectTable
	FROM DCScorecard.Rules
	WHERE [OBJECT] = @OBJECT 
	AND [SYSTEM] = @SYSTEM
	AND Release = 'R9'
	
	--create temporary table containing all column names from object table
	SELECT COLUMN_NAME AS colname
	INTO #mycols
	FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE TABLE_NAME = @TABLE_NAME

	--cycle through column names
	WHILE EXISTS(SELECT colname from #mycols)

	BEGIN
	--@delname is used to delete from temp table, @colname is used to build query
	SELECT @delname = colname, @colname = CASE colname
	--change GPID to [Employee GPID]
	WHEN 'GPID' THEN 'SRC.[' + colname + ']' + ' AS [Employee GPID]'
	ELSE 'SRC.[' + colname + ']'
	END
	FROM #mycols

	--build string of column names separated by commas
	--if @cols is empty, just put in 1st column name with no comma
	--otherwise put in a comma, then the column name
	SELECT @cols = CASE @cols
	WHEN '' THEN @colname
	ELSE @cols + ', ' + @colname
	END

	--delete to prevent infinite loop
	DELETE FROM #mycols where colname = @delname

	--END

	--drop temp table
	DROP TABLE #mycols

	PRINT(@cols)
	
	--build query string and execute query
	SET @SQL = 
		'SELECT
			EXC.RuleID,
			RUL.FieldName,
			RUL.RuleDescription,
			EXC.[Data Source ID], '
			+ @cols + ' 
			FROM 
			R9.Exceptions EXC
		INNER JOIN ' + @TABLE_NAME + ' SRC
		ON
			SRC.RecordID = EXC.RecordID
		INNER JOIN
			DCScorecard.Rules RUL
		ON
			RUL.RuleID = EXC.RuleID AND
            RUL.Display = ''1''
		WHERE
			(EXC.[Primary GPID] = ''' + @UserGPID + ''' OR EXC.[Secondary GPID] = ''' + @UserGPID + ''' OR EXISTS(SELECT GPID FROM R9.DataAdmins WHERE GPID = ''' + @UserGPID + ''')) AND EXC.[Object] = ''' + @OBJECT + ''' AND EXC.[System] = ''' + @SYSTEM + '''
		ORDER BY EXC.RuleID ASC, SRC.RecordID ASC'

	PRINT (@SQL)
	--EXEC (@SQL)

	END
END

SELECT @cols = CASE @cols
	WHEN '' THEN @colname
	ELSE @cols + ', ' + @colname
	END

SELECT @cols = CASE WHEN @cols IS NULL
	THEN @colname
	ELSE @cols + ', ' + @colname
	END