Dynamically create local temp table columns from permanent table rows

Question

BACKGROUND: I write custom SQL reports for a third-party web platform -- multiple databases, most of which are either SQL Server 2008 R2 or 2012 -- and I've run into a situation where temp tables aren't being maintained in different sections of my scripts. I haven't been able to find out whether it's just the scope that's different, or whether the sections are using separate connections. In any case, it's been a big problem for me because it's meant having to recreate temp tables multiple times from scratch, or having to create permanent tables when I'd rather not have to.

To simplify things, I wrote a stored procedure that takes the contents of a temp table (or any table, really), pivots it, and stores the columns as rows in a permanent table. This is the permanent table:

CREATE TABLE [dbo].[CustomTempRows] (
[who] [varchar](25) NOT NULL -- A session variable from the web server
,[dtdate] [datetime] NOT NULL -- The date the row was entered
,[stable] [varchar](100) NOT NULL -- The name of the source table
,[scolumn] [varchar](100) NOT NULL -- The name of the source column
,[irow] [int] NOT NULL -- The row number from the source table
,[icolumn] [int] NOT NULL -- The column number from the source table
,[itype] [int] NOT NULL -- Whether or not the column is a string, date, or number
,[dvalue] [numeric](24, 8) NULL -- The value from the source table if it's a number.
,[svalue] [nvarchar](max) NULL -- The value from the source table if it's a string.
,[dtvalue] [datetime] NULL -- The value from the source table if it's a date.
)  

The procedure takes each column of each row in the temp table and inserts it as a row in the CustomTempRows table. It requires that the temp table have an identity column called TID in order to determine the row number, which goes into irow. For simplicity, it converts values to nvarchar, numeric, or datetime -- I believe this should be okay in 99% of cases. For reference, here is the procedure:

create procedure Custom_Table_Columns_to_Rows (@who varchar(max), @dtdate datetime, @stablename varchar(max), @breverse int)
as
BEGIN
/* NOTES

1. Temp table MUST have an identity column called TID
2. The procedure strips out timestamp and sysname columns

*/

declare @temptable varchar(100)
set @temptable = '##' + convert(varchar,@@SPID) 

if @breverse = 0 /* This parameter indicates that we are inserting to the custom table 
                rather than selecting.  Not sure if selecting is actually possible
                in stored procedure */
BEGIN

/* Clean up the custom table */
delete customtemprows where (who = @who and stable = @stablename) or datediff(mi,dtdate,getdate() )>60

/* Dynamic SQL to create a global temp table and copy the user's temp table into it */
exec('if OBJECT_ID(''tempdb..' + @temptable + ''') is not null drop table ' + @temptable + ';

select * into ' + @temptable + ' from ' + @stablename )

/* Insert into customtemprows for the row and column numbers, as well as the column names and data types
(which are limited to nvarchar, numeric, and datetime -- everything is converted to one of these) 
The data columns are left blank for the moment. */
exec('
insert customtemprows (who, dtdate, stable, scolumn, irow, icolumn, itype)
select 5555
, getdate()
, ''' + @stablename + '''
, c.name
, te.tid
, c.column_id
, itype = case when t.name like ''%char%'' or t.name like ''%name%'' then 1
when t.name like (''%date%'') then 2
else 3 end

from tempdb.sys.columns c
inner join sys.types t on c.system_type_id = t.system_type_id
cross join ' + @temptable + ' te

where 
object_id = object_id(''tempdb..' + @temptable + ''')
and t.name not in (''sysname'', ''timestamp'')
and c.name<>''tid''
order by te.tid, c.column_id')

/* Create variables to use when running the loop */
declare @cols as table (icolumn int, scolumn varchar(100), itype int)
insert @cols
select distinct icolumn, scolumn, itype from customtemprows where who=@who and stable = @stablename and scolumn not in ('tid') order by icolumn

declare @icolumn int, @scolumn varchar(100), @itype int

select top 1 @icolumn = icolumn, @scolumn = scolumn, @itype = itype from @cols order by icolumn

/* Loop through as long as there are columns */
while exists (select * from @cols) 
BEGIN

/* If this column is a string, put it into the svalue column */
exec('update r
set svalue = t.' + @scolumn + '
FROM 
  customtemprows r
  inner join 
 (SELECT tid, ' + @scolumn + '
   FROM ' + @temptable + ') t on r.irow=t.tid
where
r.itype = 1
and r.icolumn = ' + @icolumn + '
and r.who = ''' + @who + ''' 
and r.stable = ''' + @stablename+ '''' ) 

/* If this column is a date, put it into the dtvalue column */
exec('update r
set dtvalue = t.' + @scolumn + '
FROM 
customtemprows r
  inner join 
     (SELECT tid, ' + @scolumn + '
   FROM ' + @temptable + ') t on r.irow=t.tid
where
r.itype = 2
and r.icolumn = ' + @icolumn + '
and r.who = ''' + @who + ''' 
and r.stable = ''' + @stablename+ '''' ) 

/* If this column is a number, put it into the dvalue column */
exec('update r
set dvalue = convert(numeric(24,10),t.' + @scolumn + ')
FROM 
  customtemprows r
  inner join 
     (SELECT tid, ' + @scolumn + '
   FROM ' + @temptable + ') t on r.irow=t.tid
where
r.itype = 3
and r.icolumn = ' + @icolumn + '
and r.who = ''' + @who + ''' 
and r.stable = ''' + @stablename+ '''' ) 

delete @cols where icolumn = @icolumn

select top 1 @icolumn = icolumn, @scolumn = scolumn, @itype = itype from @cols order by icolumn

END /* Loop */

/* Return the inserted values.  For troubleshooting */
select * from customtemprows where who = @who and stable = @stablename order by icolumn, irow
END /* @breverse = 0 */

END

MY PROBLEM is figuring out the best way to get the data out of CustomTempRows and back into a temp table for use in other sections of my reports. I've written a statement that does the job, but I'm not happy with it. I'll explain why, but first, here's the query:

declare @who varchar(100), @stablename varchar(100), @temptable varchar(100)

/* Set user variables */
set @who = '5555' /* Usually Session ID */
set @stablename = '#temp' /* The temp table you started with */
set @temptable = '##global' /* The name of the global temp table to create */

/* Drop the global temp table if it exists */
exec('if OBJECT_ID(''tempdb..' + @temptable + ''') is not null drop table ' + @temptable )

declare @columns varchar(max)
declare @rows as table (irow int, icolumn int, scolumn varchar(100), itype int, svalue nvarchar(max))
declare @irow int, @icolumn int, @scolumn varchar(100), @itype int, @svalue nvarchar(max), @convert varchar(50)
declare @sql varchar(max)

/* Create a list of columns to include in the temp table */
set @columns = '';
select @columns = @columns + ', ' + scolumn + ' ' + case r.itype when 1 then 'nvarchar(max)' when 2 then 'datetime' else 'numeric(24,10)' end + ' NULL'
FROM (select distinct icolumn, scolumn, itype from customtemprows 
where stable=@stablename and who = @who) r

select @columns = STUFF(@columns, 1, 2, '')

/* Create the global temp table and populate the TID column */
exec('create table ' + @temptable + '  (tid int, ' + @columns + ');

insert ' + @temptable + ' (tid)
select distinct irow from customtemprows where stable=''' + @stablename + ''' and who = ''' + @who + '''')

/* create data for the loop */
insert @rows
select irow, icolumn, scolumn, itype

/* Can't combine data types; convert everything to string */
, svalue = coalesce(svalue, convert(nvarchar,dvalue), convert(nvarchar,dtvalue)) 

from customtemprows where stable = @stablename and who = @who
order by icolumn, irow

select top 1 @irow = irow, @icolumn = icolumn, @scolumn = scolumn, @itype = itype, @svalue = svalue
/* For converting the string back to its previous data type */
, @convert = case when itype = 1 then 'convert(nvarchar(max),svalue)' 
    when itype = 2 then 'convert(datetime,svalue)'
    else 'convert(numeric(24,10),svalue)' end
     from @rows order by icolumn, irow

/* As long as there are rows */
while exists (select * from @rows)

BEGIN
set @sql = ''

/* Update the temp table one column at a time with data from the table variable */
select @sql = 'update t
set ' + @scolumn + ' = ' + @convert + '
from 
' + @temptable + ' t
inner join (
select irow, icolumn, scolumn, itype, svalue = coalesce(svalue,    convert(nvarchar,dvalue), convert(nvarchar,dtvalue)) 
from customtemprows where stable=''' + @stablename + ''' and who = ''' + @who + ''') r
on r.irow=t.tid and r.icolumn = ' + convert(varchar,@icolumn) 


exec(@sql)

delete @rows where icolumn = @icolumn

select top 1 @irow = irow, @icolumn = icolumn, @scolumn = scolumn, @itype = itype, @svalue = svalue
, @convert = case when itype = 1 then 'convert(nvarchar(max),svalue)' 
    when itype = 2 then 'convert(datetime,svalue)'
    else 'convert(numeric(24,10),svalue)' end
     from @rows order by icolumn, irow 


END /* Loop */

select * from ##global

It's not the most elegant solution, and I'd like to eventually replace the while loop with something set-based, but these are the bigger issues:

1. I'd like to move this (or at least most of it) into a stored procedure so that I can share it with colleagues and not have to reproduce that big block of code every time I use it.
2. I'd like to get the final results into a local rather than global temp table.

In order to do that, though, I will have to define the temp table before calling the procedure (a limitation of the platform for which I'm developing). Because I'm using dynamic SQL to create the table, however, I can't figure out how to create the table in the same scope where I need to use it.

I would also rather use a sequential number to name the global temp table, to prevent conflicts when two users access the report at the same time. But again, if I do that, the rest of my report code won't know the name of the table.

MY QUESTION: Is it even possible to define a local temp table based on data in CustomTempRows that will be available in my report scope, and then populate that table using a stored procedure based on my long query?

Thank you for making it to the end of my post, and for any suggestions you may have. It's appreciated.

Answer 1

Create a temp table with 1 column and then dynamically run dynamic SQL to add additional fields to the temp table.

DECLARE @SQL varchar(max)

CREATE TABLE #tmp(Id int)

SET @SQL = 'ALTER TABLE #tmp ADD Column1 varchar(20)'
EXEC(@SQL)