0

I would like to create a table for statement: DBCC CHECKDB (0) WITH ALL_ERRORMSGS, TABLERESULTS however the result of this statement depends on SQL Server version, in higher versions there are more columns.

Statement:

IF (CAST(SERVERPROPERTY('productversion') AS varchar), 2) > '10'    
    CREATE TABLE #dbcc_output (columns..)
ELSE        
    CREATE TABLE #dbcc_output (different columns..)

This code causes an error:

There is already an object named '#dbcc_output' in the database

Is there a smart way to do it or just check if object exists in ELSE part?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
I.P.
  • 300
  • 2
  • 16
  • This is a parsing error, you can't try to create the same object in the same batch more than once, even if it is *impossible* for both statements to be run. – Thom A Sep 09 '21 at 12:02
  • 2
    Why not just create a table will all the columns you need, regardless of the version? Then you don't need to try and create 2 different versions. – Thom A Sep 09 '21 at 12:07
  • If you're doing something like this `INSERT #dbcc_output (..) EXEC ('DBCC CHECKDB (mydb) WITH ALL_ERRORMSGS, TABLERESULTS')`, which requires the exact columns, then you'll need to create your temp table and run the DBCC together with dynamic SQL. – squillman Sep 09 '21 at 12:20
  • Or drop support for SQL 2008, which is no longer supported by Microsoft. – David Browne - Microsoft Sep 09 '21 at 15:31

0 Answers0