Transact SQL – Create Object with different Structure based on runtime decisions

Background

Based on the version of SQL Server being targeted one might need to create a slightly different table structure.

Here is the original code that fails.

And, slight variations.

Implementation

Original

Code


set nocount on;
go

declare @productVersion varchar(30)
declare @productVersionMajor int

declare @iVersionMajor2012 int

set @iVersionMajor2012 = 12

set @productVersion = cast(serverproperty('ProductVersion') as varchar(30))
set @productVersionMajor = parseName(@productVersion, 4)

print '@productVersion : ' + @productVersion
print '@productVersionMajor : ' + cast(@productVersionMajor as varchar(30))


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

	print 'Droping table #LogInfo ... '

	drop table #LogInfo 

end
 
if (@productVersionMajor >= @iVersionMajor2012)
begin

	print 'Create table #LogInfo #v2012 ...'

	CREATE TABLE #LogInfo
	(
		  [RecoveryUnitID] int NULL -- Added on 20161109
		, [FileID]	       BIGINT
		, [FileSize]	   BIGINT
		, [StartOffset]    BIGINT
		, [FSeqNo]         BIGINT
		, [Status]		   BIGINT
		, [Parity]		   BIGINT
		, [CreateLSN]      VARCHAR(50) 
	) 

	set noexec on

end
else
begin

	print 'Create table #LogInfo #v2012 (pre) ...'

	CREATE TABLE #LogInfo
	(
  		  [FileID]	       BIGINT
		, [FileSize]	   BIGINT
		, [StartOffset]    BIGINT
		, [FSeqNo]         BIGINT
		, [Status]		   BIGINT
		, [Parity]		   BIGINT
		, [CreateLSN]      VARCHAR(50) 
	) 


end
go 

insert into #LogInfo 
EXECUTE ('DBCC LOGINFO with no_infomsgs') 

 
select
          tblSysfile.[name]
        , tblLogInfo.[FileID]
        , count(*) as cnt 
 
from #LogInfo tblLogInfo 
 
inner join sys.database_files tblSysfile 
        on tblLogInfo.FileID = tblSysfile.[file_id]
 
group by
          tblSysfile.[name]
        , tblLogInfo.[FileID]
 
order by COUNT(*) desc
 
if object_id('tempdb..#LogInfo') is not null
begin

	print 'Droping table #LogInfo ... '

	drop table #LogInfo 

end
go

Output


Msg 2714, Level 16, State 1, Line 53
There is already an object named '#LogInfo' in the database.
Msg 208, Level 16, State 0, Line 67
Invalid object name '#LogInfo'.


Revised ( Add Go Statement )

Premise:

We add a go to ensure object is in fact created.

Code


set nocount on;
go

declare @productVersion varchar(30)
declare @productVersionMajor int

declare @iVersionMajor2012 int

set @iVersionMajor2012 = 12

set @productVersion = cast(serverproperty('ProductVersion') as varchar(30))
set @productVersionMajor = parseName(@productVersion, 4)

print '@productVersion : ' + @productVersion
print '@productVersionMajor : ' + cast(@productVersionMajor as varchar(30))


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

	print 'Droping table #LogInfo ... '

	drop table #LogInfo 

end
 
if (@productVersionMajor >= @iVersionMajor2012)
begin

	print 'Create table #LogInfo #v2012 ...'

	CREATE TABLE #LogInfo
	(
		  [RecoveryUnitID] int NULL -- Added on 20161109
		, [FileID]	       BIGINT
		, [FileSize]	   BIGINT
		, [StartOffset]    BIGINT
		, [FSeqNo]         BIGINT
		, [Status]		   BIGINT
		, [Parity]		   BIGINT
		, [CreateLSN]      VARCHAR(50) 
	) 

end

/* 
	This go is needed to ensure that object is indeed create and that object_id check succeed once object is created 
*/
go

if object_id('#LogInfo') is null
begin

	print 'Create table #LogInfo #v2012 (pre) ...'

	CREATE TABLE #LogInfo
	(
  		  [FileID]	       BIGINT
		, [FileSize]	   BIGINT
		, [StartOffset]    BIGINT
		, [FSeqNo]         BIGINT
		, [Status]		   BIGINT
		, [Parity]		   BIGINT
		, [CreateLSN]      VARCHAR(50) 
	) 


end
go 

insert into #LogInfo 
EXECUTE ('DBCC LOGINFO with no_infomsgs') 

 
select
          tblSysfile.[name]
        , tblLogInfo.[FileID]
        , count(*) as cnt 
 
from #LogInfo tblLogInfo 
 
inner join sys.database_files tblSysfile 
        on tblLogInfo.FileID = tblSysfile.[file_id]
 
group by
          tblSysfile.[name]
        , tblLogInfo.[FileID]
 
order by COUNT(*) desc
 
if object_id('tempdb..#LogInfo') is not null
begin

	print 'Droping table #LogInfo ... '

	drop table #LogInfo 

end
go

Revised ( Global Table)

Premise:

If we go with a global temp table, we can enclose the creation in an exec statement.

As global temp table are available to all sessions, they will be available to the current session.

Code


use [tempdb]
go

set nocount on;
go

declare @productVersion varchar(30)
declare @productVersionMajor int

declare @iVersionMajor2012 int

set @iVersionMajor2012 = 12

set @productVersion = cast(serverproperty('ProductVersion') as varchar(30))
set @productVersionMajor = parseName(@productVersion, 4)

print '@productVersion : ' + @productVersion
print '@productVersionMajor : ' + cast(@productVersionMajor as varchar(30))


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

	print 'Dropping table ##LogInfo ... '

	drop table ##LogInfo 

end
 
if (@productVersionMajor >= @iVersionMajor2012)
begin

	print 'Create table #LogInfo #v2012 ...'

	exec('
	CREATE TABLE ##LogInfo
	(
		  [RecoveryUnitID] int NULL -- Added on 20161109
		, [FileID]	       BIGINT
		, [FileSize]	   BIGINT
		, [StartOffset]    BIGINT
		, [FSeqNo]         BIGINT
		, [Status]		   BIGINT
		, [Parity]		   BIGINT
		, [CreateLSN]      VARCHAR(50) 
	) 
	')

	set noexec on

end
else
begin

	print 'Create table #LogInfo #v2012 (pre) ...'

	exec('
			CREATE TABLE ##LogInfo
			(
				  [FileID]	       BIGINT
				, [FileSize]	   BIGINT
				, [StartOffset]    BIGINT
				, [FSeqNo]         BIGINT
				, [Status]		   BIGINT
				, [Parity]		   BIGINT
				, [CreateLSN]      VARCHAR(50) 
			) 
		')

end
 
set noexec off

insert into ##LogInfo 
EXECUTE ('DBCC LOGINFO with no_infomsgs') 

 
select
          tblSysfile.[name]
        , tblLogInfo.[FileID]
        , count(*) as cnt 
 
from ##LogInfo tblLogInfo 
 
inner join sys.database_files tblSysfile 
        on tblLogInfo.FileID = tblSysfile.[file_id]
 
group by
          tblSysfile.[name]
        , tblLogInfo.[FileID]
 
order by COUNT(*) desc
 
if object_id('tempdb..##LogInfo') is not null
begin

	print 'Dropping table ##LogInfo ... '

	drop table ##LogInfo 

end
go

Summary

So to get around the “Msg 2714, Level 16, State 1” or “There is already an object named #[table] in the database”, we have a couple of options.

Those options are

  1. Make sure that you have a go statement post object creation
  2. Create Global variables enclosed in an if object_id; with an exec statement

 

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s