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.

Posted by: MR. JOYDEEP DAS