Pages

Thursday, May 8, 2014

Temporary Table with Dynamic Columns in SP

What the Problem is


When we are working with temporary table on a stored procedure with dynamic columns name we have some problem.

To understand it properly we are just taking an example.

BEGIN
     DECLARE @TblCol nVARCHAR(max);
     CREATE TABLE #TMPDATE
             (date1  DATETIME);
     INSERT INTO #TMPDATE
             (date1)
     VALUES ('2014-01-01'),('2014-01-02'),('2014-01-03'),
            ('2014-01-04'),('2014-01-05'),('2014-01-06');                
     SELECT @TblCol  =
     STUFF(( SELECT DISTINCT TOP 100 PERCENT
                   '] DECIMAL(20,2), [' + t2.date1
          FROM    (
                    SELECT CONVERT(VARCHAR(25),date1,105)date1
                    FROM  #TMPDATE
                    ) AS t2
                    ORDER BY '] DECIMAL(20,2), [' +t2.date1
          FOR XML PATH('')
       ), 1, 2, '') + '] DECIMAL(20,2)'

     SET @TblCol = SUBSTRING(@TblCol,LEN('VARCHAR(20,2),')+2, LEN(@TblCol));
     SET @TblCol = 'CREATE TABLE #tmp_Example ('+ @TblCol+')';
     EXEC sp_executesql @TblCol;
    
     -- Here i am trying to Use temp table
     SELECT * FROM #tmp_Example;
END

Here in this example temp table named #temp_Example has variable columns depending on the value of temp table #TEMPDATE.

Here the table named #tmp_Example is created successfully but not expose it, so we cannot use this tempt able within the procedure.

Solutions of the Problem

BEGIN
     DECLARE @TblCol nVARCHAR(max);
     CREATE TABLE #TMPDATE
             (date1  DATETIME);
     INSERT INTO #TMPDATE
             (date1)
     VALUES ('2014-01-01'),('2014-01-02'),('2014-01-03'),
            ('2014-01-04'),('2014-01-05'),('2014-01-06');
     CREATE TABLE #tmp_Example
            (COL  DECIMAL);
                                     
     SELECT @TblCol  =
     STUFF(( SELECT DISTINCT TOP 100 PERCENT
                   '] DECIMAL(20,2), [' + t2.date1
          FROM    (
                    SELECT CONVERT(VARCHAR(25),date1,105)date1
                    FROM  #TMPDATE
                    ) AS t2
                    ORDER BY '] DECIMAL(20,2), [' +t2.date1
          FOR XML PATH('')
       ), 1, 2, '') + '] DECIMAL(20,2)'

     SET @TblCol = SUBSTRING(@TblCol,LEN('VARCHAR(20,2),')+2, LEN(@TblCol));
     SET @TblCol = 'ALTER TABLE #tmp_Example ADD'+@TblCol;
     EXEC sp_executesql @TblCol;
     ALTER TABLE #tmp_Example DROP COLUMN COL;
    
     -- Here i am trying to Use temp table
     SELECT * FROM #tmp_Example;
END

Here we create the temp table named #tmp_Example first and then we just alter it and drop the extra columns used in creation.

Output is
01-01-2014     02-01-2014
----------     ---------- …. n
(0 row(s) affected)

Hope you like it.

No comments:

Post a Comment