ALTER PROCEDURE [dbo].[MDSP_LookUpTableCols]
@TableName varchar(200)
AS
--Declare variables
DECLARE @FinalResults varchar(4000)
DECLARE @ColumnNames varchar(1000)
DECLARE @numbercolumnfound int
DECLARE @Abbrv_hldr varchar(16)
--Create a table with desired column names to return to the report
CREATE TABLE #Final_results (
[ABBRV] varchar(16),
[DESC] varchar(2000),
[NAME] varchar(300),
)
--get column names for particualr table
SELECT name
INTO #LKUP_TableData
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@TableName)
AND Name in ('Code','Abbreviation','Name','Description');
Set @numbercolumnfound = (SELECT count(*) from #LKUP_TableData)
IF @numbercolumnfound = 3
BEGIN
--Set the column names into a comma separated list
Set @ColumnNames = (SELECT DISTINCT ColumnName =
substring((SELECT DISTINCT ( ', ' + [Name] )
FROM #LKUP_TableData
FOR XML PATH( '' )),3,1000))
/*utilize comma separated column list in dynamic SQL
Statement to get variable table name
and columns from that table*/
Set @FinalResults = 'select '+ @ColumnNames +' from ' + @TableName
/*Execute the dynamic sql to insert the data into the
table to return it to the report*/
INSERT INTO #Final_results
EXEC(@FinalResults)
END
IF @numbercolumnfound = 2
BEGIN
--Set the column names into a comma separated list
Set @ColumnNames = (SELECT DISTINCT ColumnName =
substring((SELECT DISTINCT ( ', ' + [Name] )
FROM #LKUP_TableData
FOR XML PATH( '' )),3,1000))
IF @ColumnNames = 'Description, Name'
BEGIN
/*utilize comma separated column list in dynamic SQL
Statement to get variable table name
and columns from that table*/
Set @FinalResults = 'select Abbrv = '+'''No ABBRV'''+', '+ @ColumnNames +' from ' + @TableName
/*Execute the dynamic sql to insert the data into
the table to return it to the report*/
INSERT INTO #Final_results
EXEC(@FinalResults)
END
IF @ColumnNames = 'Code, Description'
BEGIN
/*utilize comma separated column list in dynamic SQL
Statement to get variable table name
and columns from that table*/
Set @FinalResults = 'select '+ @ColumnNames +', '+'Name = '+'''Name does not exist in this table'''+' from ' + @TableName
/*Execute the dynamic sql to insert the data into
the table to return it to the report*/
INSERT INTO #Final_results
EXEC(@FinalResults)
END
IF @ColumnNames = 'Abbreviation, Description'
BEGIN
/*utilize comma separated column list in dynamic SQL
Statement to get variable table name
and columns from that table*/
Set @FinalResults = 'select '+ @ColumnNames +', '+'Name = '+'''Name does not exist in this table'''+' from ' + @TableName
/*Execute the dynamic sql to insert the data into
the table to return it to the report*/
INSERT INTO #Final_results
EXEC(@FinalResults)
END
END
IF @numbercolumnfound = 1
BEGIN
--Set the column names into a comma separated list
Set @ColumnNames = (SELECT DISTINCT ColumnName = substring((SELECT DISTINCT ( ', ' + [Name] )
FROM #LKUP_TableData
FOR XML PATH( '' )),3,1000))
IF @ColumnNames = 'Abbreviation'
BEGIN
/*utilize comma separated column list in dynamic SQL
Statement to get variable table name
and columns from that table*/
Set @FinalResults = 'select '+ @ColumnNames +', '+'Description = '+'''Description does not exist in this table'''+', '+'Name = '+'''Name does not exist in this table'''+' from ' + @TableName
/*Execute the dynamic sql to insert the data into
the table to return it to the report*/
INSERT INTO #Final_results
EXEC(@FinalResults)
END
IF @ColumnNames = 'Code'
BEGIN
/*utilize comma separated column list in dynamic SQL
Statement to get variable table name
and columns from that table*/
Set @FinalResults = 'select '+ @ColumnNames +', '+'Description = '+'''Description does not exist in this table'''+', '+'Name = '+'''Name does not exist in this table'''+' from ' + @TableName
/*Execute the dynamic sql to insert the data into
the table to return it to the report*/
INSERT INTO #Final_results
EXEC(@FinalResults)
END
IF @ColumnNames = 'Description'
BEGIN
/*utilize comma separated column list in dynamic SQL
Statement to get variable table name
and columns from that table*/
Set @FinalResults = 'select Abbrv = '+'''No ABBRV'''+', '+ @ColumnNames +', '+'Name = '+'''Name does not exist in this table'''+' from ' + @TableName
/*Execute the dynamic sql to insert the data into
the table to return it to the report*/
INSERT INTO #Final_results
EXEC(@FinalResults)
END
IF @ColumnNames = 'Name'
BEGIN
/*utilize comma separated column list in dynamic SQL
Statement to get variable table name
and columns from that table*/
Set @FinalResults = 'select Abbrv = '+'''No ABBRV'''+', '+'Description = '+'''Description does not exist in this table'''+', '+ @ColumnNames +' from ' + @TableName
/*Execute the dynamic sql to insert the data into
the table to return it to the report*/
INSERT INTO #Final_results
EXEC(@FinalResults)
END
END
IF @numbercolumnfound < 1
/*If no fields exist in the given table, create placeholders
for each of the desired fields and a default value for each.*/
BEGIN
INSERT INTO #Final_results
SELECT [ABBRV] = 'No ABBRV',[DESC] = 'Description does not exist in this table', [NAME] = 'Name does not exist in this table'
END
--return results to report
SELECT *,PKTableName=@TableName FROM #Final_Results
