Dynamic SQL

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 frominformation_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

Our Office

Office Hours:
Monday thru Friday
8:30am to 5:30pm EST

Address:
1301 North Summit
Toledo, Ohio 43604 USA

Phone:
(419) 243-7445

Let's talk.

Have an idea or project you'd like to discuss? Contact us by filling out the form below for your digital, web, and software solutions!

Contact Us

To help us further reduce spam