Transact SQL :- Temporary Table & Conditional Creation

Background

Transact SQL offers the best programming space of all the top tier relational database.

But, there are areas one can stumble over, as well.

Temporary Table

Simple

Let us create a simple temporary table


create table #temp
(
   [id] tinyint not null
        identity(1,1)
)

Different Structure

Objective

Depending on certain conditions, our table structure might need to be different.

  1. Column 2
    • If @type is 1
      • Issue a default of 1
    • If @type is 2
      • Issue a default of 2

Code

Create Table Variable – Regular

SQL

declare @type tinyint

set @type=1

if (@type=1)
begin

    create table #temp
   (
      [id] tinyint not null
        identity(1,1)

      , [type] tinyint not null
          default 1

   )

end

else if (@type=2)
begin

    create table #temp
   (
        [id] tinyint not null
          identity(1,1)

      , [type] tinyint not null
          default 2

   )

end

Output
Output – Image

Msg2714.01.20191103.0308AM

Output – Text
Msg 2714, Level 16, State 1, Line 36
There is already an object named '#temp' in the database.

Create Temporary Table – Using Exec Statement

Outline

I can see it now, you say to yourself, you will use an exec statement and conditionally create the table.

SQL

set nocount on
go

set XACT_ABORT on
go

declare @type tinyint
declare @checkBeforeUse bit

set @checkBeforeUse =0
--set @checkBeforeUse =1

set @type =1

/*
    --set @type =2
    --set @type =3
*/

if object_id('tempdb..#temp') is not null
begin

    drop table #temp

end

if (@type=1)
begin

    exec('
    create table #temp
    (
          [id] tinyint not null
               identity(1,1)

        , [type] tinyint not null default 1

    )
    ')

end
else if (@type=2)
begin

    exec('
        create table #temp
        (
              [id] tinyint not null
                   identity(1,1)

            , [type] tinyint not null default 2

        )
    ')

end
else
begin

    raiserror('Invalid Type', 16,1)

    return

end

if (@checkBeforeUse=0)
begin

    insert into #temp default values;

    select *

    from   #temp

end
else if (@checkBeforeUse=1)
begin

    if object_id('tempdb..#temp') is not null
    begin

        insert into #temp default values;

        select *

        from   #temp

        drop table #temp

    end
    else
    begin

        raiserror
        (
              'Temp Table #temp does not exist'
            , 16
            ,1
        )

    end

end

if object_id('tempdb..#temp') is not null
begin

    drop table #temp

end

Output

Output – Image

Msg.208.InvalidObjectName.01.20191103.0323AM.PNG

Output – Text

Msg 208, Level 16, State 0, Line 71
Invalid object name '#temp'.

Explanation

  1. The table was likely created, but it was created in a session that was closed/cleaned upon completion of the exec statement

Create Global Temporary Table – Using Exec Statement

Outline

Rather than create temporary temp table;  let us create Global Temp table.

SQL
set nocount on
go

set XACT_ABORT on
go

declare @type tinyint

set @type =1

/*
    --set @type =2
    --set @type =3
*/

if object_id('tempdb..##temp') is not null
begin

    drop table ##temp

end

if (@type=1)
begin

    exec('
    create table ##temp
    (
          [id] tinyint not null
               identity(1,1)

        , [type] tinyint not null default 1

    )
    ')

end
else if (@type=2)
begin

    exec('
        create table ##temp
        (
              [id] tinyint not null
                   identity(1,1)

            , [type] tinyint not null default 2

        )
    ')

end
else
begin

    raiserror('Invalid Type', 16,1)

    return

end

if object_id('tempdb..##temp') is not null
begin

    insert into ##temp default values;

    select *

    from   ##temp

    drop table ##temp

end 

Output

Output – Image

Table.Temp.global.01.20191103.0333AM

Explanation

  1. Things are as good as they will be

Summary

Again, Transact SQL likely offers the most accessible SQL programming toolset.

But, watch out for name resolution and collision issues based on how the parser does its work.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s