While working on a project I was challenged to build a table that showed only the columns of data associated with the current family of data. The challenge was the database I was pulling from had over a hundred columns and hundreds of rows. I only wanted to show the columns associated with the products showing in the table so I needed a way to remove the unneeded columns from the table. This could be done by hiding the unneeded columns after the data was retrieved but it was time intensive to pull all the columns and then hide some of them. What I chose to do was to build the table completely during the SQL call using Dynamic SQL methods.
In order to return the final table, I needed to know the name of the column and what data was stored there. In order to find the name of a column one must use the method COL_NAME; this method had two parameters: Table ID and the column index. In order for this method to work you must know the number of columns in the table. To find the number of columns in a table, call a query from information_schema:
Now that the number of columns is known, a loop can be created to find what columns have data:
To simply make a table that only has columns with data, the following can be run inside of the loop:
The issue with the above is when the final table is returned the column names are set and would need to be changed. To avoid having to change the column after the table is returned, I used a reference table that held the corrected names of the columns and created a selecting variable with the right columns and names:
Once all of these queries are done, the temporary table can be queried and returned:
The final table displays only the columns that have data in them. By using Dynamic SQL, I was able to build a list of items with only the needed information. The following is the procedure code:
ALTER PROCEDURE proc_MerchandiseTbl
(
@MerchandiseID uniqueidentifier
)
AS
BEGIN
CREATE TABLE #tmpTbl(
.
.
.
)
INSERT INTO #tmpTbl (
.
.
.
)
SELECT *
FROM [Merchandise]
WHERE([MerchandiseID] = @MerchandiseID)
CREATE TABLE #currentItem([CurrentItem] [nvarchar](4000) NULL)
DECLARE @i int
DECLARE @count int
DECLARE @colName nvarchar(1000)
DECLARE @current nvarchar(1000)
DECLARE @selectMethod nvarchar(4000)
DECLARE @header nvarchar(1000)
SET @selectMethod = ”
SELECT @count = COUNT(*)
FROM information_schema.columns
WHERE table_name = ‘Merchandise’
SET @i = 1
WHILE (@i <= @count)
BEGIN
SELECT @colName = COL_NAME(OBJECT_ID(‘Merchandise’),@i)
EXECUTE(‘DECLARE
@LocalCurrent nvarchar(1000)SELECT
TOP(1) @LocalCurrent = ‘ + @colName + ‘ FROM #tmpTbl
INSERT INTO #currentItem VALUES(@LocalCurrent)‘)
SELECT TOP(1)@current = [CurrentItem] FROM #currentItem
DELETE FROM #currentItem
IF @current IS NOT NULL
BEGIN
SELECT @header = SpecName
FROM Specifications
WHERE SpecField = @colName
END
IF @i > 1
BEGIN
SET @selectMethod = @selectMethod + ‘, ‘
END
SET @selectMethod = @selectMethod + QUOTENAME(@colName)
IF @header IS NOT NULL
BEGIN
SET @selectMethod = @selectMethod + ‘ “‘ + @header + ‘”‘
SET @header = NULL
END
SET @i = @i + 1;
END
EXECUTE(‘Select’ + @selectMethod + ‘ FROM #tmpTbl’)
DROP TABLE #tmpTbl
DROP TABLE #currentItem
RETURN 0
END
Follow Us!