SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
thisted
thisted
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 343
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






Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26024 Visits: 17528
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
thisted
thisted
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 343
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. Sad
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search