none
if else issue

    Question

  • Declare @Version int;




    select @version=(SUBSTRING(CONVERT(VARCHAR(50),SERVERPROPERTY('productversion')), 1,2))


    if (@version >=11)

    begin 
    IF OBJECT_ID('tempdb..#checkresults') IS NOT NULL

    DROP TABLE #checkresults


    create table #checkresults 
    (

    [Error] [int] NULL,
    [Level] [int] NULL,
    [State] [int] NULL,
    [MessageText] [varchar](650) NULL,
    [RepairLevel] [varchar](150) NULL,
    [Status] [int] NULL,
    [DbId] [int] NULL,
    [DbFragId] [int] NULL,
    [ObjectId] [int] NULL,
    [IndexId] [int] NULL,
    [PartitionId] [int] NULL,
    [AllocUnitId] [int] NULL,
    [RidDbId] [int] NULL,
    [RidPruId] [int] NULL,
    [File] [int] NULL,
    [Page] [int] NULL,
    [Slot] [int] NULL,
    [RefDbId] [int] NULL,
    [RefPruId] [int] NULL,
    [RefFile] [int] NULL,
    [RefPage] [int] NULL,
    [RefSlot] [int] NULL,
    [Allocation] [int] NULL )

    end


    else 



    begin


    IF OBJECT_ID('tempdb..#checkresults') IS NOT NULL

    DROP TABLE #checkresults

    create table #checkresults
    (

    [Error] [int] NULL,
    [Level] [int] NULL,
    [State] [int] NULL,
    [MessageText] [varchar](650) NULL,
    [RepairLevel] [varchar](150) NULL,
    [Status] [int] NULL,
    [DbId] [int] NULL,
    --[DbFragId] [int] NULL,
    [ObjectId] [int] NULL,
    [IndexId] [int] NULL,
    [PartitionId] [int] NULL,
    [AllocUnitId] [int] NULL,
    --[RidDbId] [int] NULL,
    --[RidPruId] [int] NULL,
    [File] [int] NULL,
    [Page] [int] NULL,
    [Slot] [int] NULL,
    --[RefDbId] [int] NULL,
    --[RefPruId] [int] NULL,
    [RefFile] [int] NULL,
    [RefPage] [int] NULL,
    [RefSlot] [int] NULL,
    [Allocation] [int] NULL )

    end 


    select * from #checkresults


    Msg 2714, Level 16, State 1, Line 59
    There is already an object named '#checkresults' in the database.


    this is the error i am getting can u please help me
     
    Thursday, September 26, 2013 12:37 AM

Answers

  • Refer http://support.microsoft.com/kb/295305/en-us

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    • Marked as answer by ashwin08 Thursday, September 26, 2013 12:11 PM
    Thursday, September 26, 2013 12:52 AM
    Moderator
  • Split it into 2 batches; the first to drop the table if exists and the second to create it:

    IF OBJECT_ID('tempdb..#checkresults') IS NOT NULL
         DROP TABLE #checkresults
    GO
    
    select @version=(SUBSTRING(CONVERT(VARCHAR(50),SERVERPROPERTY('productversion')), 1,2))
    if (@version >=11)
    begin 
        create table #checkresults 
        (...)
    END
    ELSE
    BEGIN
        create table #checkresults 
        (...)
    END


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Olaf,

    That might not work again because parser would block it.

    KB which i mentioned earlier has a workaround to create table once and alter in if .. else block. Other workaround would be to use permanent table instead of temp .


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    • Marked as answer by ashwin08 Thursday, September 26, 2013 1:00 PM
    Thursday, September 26, 2013 5:26 AM
    Moderator

All replies

  • Refer http://support.microsoft.com/kb/295305/en-us

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    • Marked as answer by ashwin08 Thursday, September 26, 2013 12:11 PM
    Thursday, September 26, 2013 12:52 AM
    Moderator
  • This looks like the standard parsing error (which I believe to be invalid), because you have two creates, wrapped in the same if (plus you probably do an insert into).

    Try this.

    instead of using Else

    do select if's

    if (@version >=11)

    begin

    end

    if (@version < 11)

    begin

    end

    Thursday, September 26, 2013 1:02 AM
  • Same issue using only if 
    Thursday, September 26, 2013 1:11 AM
  • Split it into 2 batches; the first to drop the table if exists and the second to create it:

    IF OBJECT_ID('tempdb..#checkresults') IS NOT NULL
         DROP TABLE #checkresults
    GO
    
    select @version=(SUBSTRING(CONVERT(VARCHAR(50),SERVERPROPERTY('productversion')), 1,2))
    if (@version >=11)
    begin 
        create table #checkresults 
        (...)
    END
    ELSE
    BEGIN
        create table #checkresults 
        (...)
    END


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, September 26, 2013 4:44 AM
  • Split it into 2 batches; the first to drop the table if exists and the second to create it:

    IF OBJECT_ID('tempdb..#checkresults') IS NOT NULL
         DROP TABLE #checkresults
    GO
    
    select @version=(SUBSTRING(CONVERT(VARCHAR(50),SERVERPROPERTY('productversion')), 1,2))
    if (@version >=11)
    begin 
        create table #checkresults 
        (...)
    END
    ELSE
    BEGIN
        create table #checkresults 
        (...)
    END


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Olaf,

    That might not work again because parser would block it.

    KB which i mentioned earlier has a workaround to create table once and alter in if .. else block. Other workaround would be to use permanent table instead of temp .


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    • Marked as answer by ashwin08 Thursday, September 26, 2013 1:00 PM
    Thursday, September 26, 2013 5:26 AM
    Moderator