Error using If Logic to create specific version of a table.

Question

Error using If Logic to create specific version of a table.

thisted

SSCrazy

Points: 2131
More actions
January 29, 2013 at 8:43 am

#267818

When I parse the code below, I get the error 'There is already an object named '#DBCC_OUTPUT' in the database.'
I don't want to resort to dynamic SQL if I can avoid it. I didn't include all the columns in the create clause for brevity's sake.
DECLARE @ProdVersion VARCHAR(4)
SELECT @ProdVersion = CASE
WHEN SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1, 3) = '9.0'
THEN '2005'
WHEN SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1, 3) = '10.'
THEN '2008'
WHEN SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1, 3) = '11.'
THEN '2012'
END
IF (@ProdVersion = '2012')
BEGIN
--Create the table with extra columns added in 2012
CREATE TABLE #DBCC_OUTPUT (
[Error] INT NULL
)
END
ELSE
BEGIN
--Create the table with the columns generated in 2005/2008
CREATE TABLE #DBCC_OUTPUT (
[Error] INT NULL
)
END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

The parser doesn't care about logical processing. It sees the same create temp table and throws up its hands. Maybe you can change your logic a little bit to avoid dynamic sql. In your comments it sounds like you have the same columns in 2005/2008 but with 2012 you need to simply add some columns? If so, just create your 2005 version of the table and then add whatever columns you need for 2012.

DECLARE @ProdVersion VARCHAR(4)

SELECT @ProdVersion = CASE

WHEN SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1, 3) = '9.0'

THEN '2005'

WHEN SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1, 3) = '10.'

THEN '2008'

WHEN SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1, 3) = '11.'

THEN '2012'

END

CREATE TABLE #DBCC_OUTPUT

(

[Error] INT NULL

)

IF (@ProdVersion = '2012')

BEGIN

--Alter the table with extra columns added in 2012

alter TABLE #DBCC_OUTPUT

add YourNewColumn varchar(10)

alter TABLE #DBCC_OUTPUT

add YourOtherColumn int

END

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

thisted SSCrazy Points: 2131 More actions · Answer 2

I'm inserting the output of

EXECUTE ('DBCC CHECKDB([' + @DBname + ']) WITH TABLERESULTS');

In 2012 there's additional columns. Trying to make the script I use in 2008 work with 2012 so I only have to maintain 1 script.

I ended up creating 2 different temp tables, based on the version, and then had to use if logic for all of my statements. 🙁