In this post I’m going to describe how to create and use a dynamic column in a temporary table. Let’s get started.
[Warning: dynamic sql is used here so use this tip with the appropriate security considerations]
Lets say you need to create a temporary table in which one of the column’s length must be dynamic.
Example
You’d like the size of column in a temp table to be change based on some condition such as declare the width of a column to be the same size as the length of another column or data (possibly from a different table or concatenations or a million other things).
Naive First attempt…
DECLARE @dynamicLength int = LEN('123' + '45') ;
DECLARE @dynamicColumn NVARCHAR(MAX) = 'dynamicColumn CHAR(' + CAST(@dynamicLength AS NVARCHAR) + ')';
DECLARE @dynamicSQL NVARCHAR(MAX) = 'CREATE TABLE #dynTestTable ( pk int,' + @dynamicColumn + ' )';
-- DEBUG
SELECT @dynamicLength AS dynamicLength,
@dynamicColumn AS dynamicColumn,
@dynamicSQL AS dynamicSQL ;
EXECUTE ( @dynamicSQL ) ;
INSERT INTO #dynTestTable
VALUES ( 1, 'abcde' ),
( 2, 'bcdef' ) ;
After executing…
Msg 208, Level 16, State 0, Line 14 Invalid object name '#dynTestTable'.
The reason for this error is because when EXECUTE runs, it literally creates the temp table in a different session and returns back to the current code path with no change to the local environment. Thus, we are unable to do anything on our #dynTestTable.
One viable alternative is to use global tables…
-- be sure to delete the temp table if it exists first
DECLARE @dynamicLength int = LEN('123' + '45') ;
DECLARE @dynamicColumn NVARCHAR(MAX) = 'dynamicColumn CHAR(' + CAST(@dynamicLength AS NVARCHAR) + ')' ;
DECLARE @dynamicSQL NVARCHAR(MAX) = 'CREATE TABLE ##dynTestTable ( pk int,' + @dynamicColumn + ' )' ;
EXECUTE ( @dynamicSQL ) ;
INSERT INTO ##dynTestTable
VALUES ( 1, 'abcde' ),
( 2, 'bcdef' ) ;
SELECT *
FROM ##dynTestTable ;
Results…
(2 row(s) affected) pk dynamicColumn ----------- ------------- 1 abcde 2 bcdef (2 row(s) affected)
The global temp table will work…so long as nobody else is doing anything with it (like dropping it). If you’re absolutely sure nothing else will be mucking with your global table then this could work. Another, safer, alternative is to declare the temp table first then alter it with dynamic sql. This gives you exclusive access to the table and no other process can meddle it up while you’re working with it.
IF OBJECT_ID('tempdb.dbo.#dynTestTable') is not null
DROP TABLE #dynTestTable;
CREATE TABLE #dynTestTable ( pk INT ) ;
DECLARE @dynamicLength int = LEN('123' + '45') ;
DECLARE @dynamicColumn NVARCHAR(MAX) = 'dynamicColumn CHAR(' + CAST(@dynamicLength AS NVARCHAR) + ')' ;
DECLARE @dynamicSQL NVARCHAR(MAX) = 'ALTER TABLE #dynTestTable ADD ' + @dynamicColumn + ';' ;
-- DEBUG
SELECT @dynamicLength AS dynamicLength,
@dynamicColumn AS dynamicColumn,
@dynamicSQL AS dynamicSQL ;
EXECUTE ( @dynamicSQL ) ;
INSERT INTO #dynTestTable
VALUES ( 1, 'abcde' ),
( 2, 'bcdef' ) ;
SELECT *
FROM #dynTestTable ;
Results:
(2 row(s) affected) pk dynamicColumn ----------- ------------- 1 abcde 2 bcdef (2 row(s) affected)
There you have it.
[wpfilebase tag=list id=1 pagenav=1 /]
