0

I noticed some behavior I found weird while editing a co-workers stored procedure:

    create procedure weird_behavior (@switch bit)

    as

    if (@switch = 0)
    begin
        select *
        into #tables
        from sys.tables st
        where left(st.name, 1) in ('a','b','c')
    end

    else if (@switch = 1)
    begin
        select * 
        into #tables
        from sys.tables st
        where left(st.name, 1) in ('d','e','f')
    end

    select * from #tables

    --drop procedure weird_behavior

you get the error:

There is already an object named '#tables' in the database.

This seems odd to me, since only one of the select * into will ever run. I'm forced to rewrite it like so:

    create procedure weird_behavior (@switch bit)

    as

    if (@switch = 0)
    begin
        select *
        into #tables
        from sys.tables st
        where left(st.name, 1) in ('a','b','c')
    end

    else if (@switch = 1)
    begin
        select * 
        into #tables01
        from sys.tables st
        where left(st.name, 1) in ('d','e','f')
    end

    select * from #tables

    --drop procedure weird_behavior

This would cause headaches because sometimes the output is in #tables, and sometimes it is in #tables01.

Question: why won't SQL Server let me write this code?

marked as duplicate by Erik Darling sql-server Apr 16 at 15:08

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Why not create the temp table first and then run an insert into command for the IF statements? – Padwan Apr 16 at 14:47
  • @Padwan then you've got to spell out all the columns. Not a big deal if you've got only a few columns that don't change, but using into, it will inherit properties from the original table. – James Apr 16 at 14:48
  • But couldn't you then just put select * from #tables and select * from #tables01 inside their respective IF blocks? Or just do select * into #tables from sys.tables where 1 = 0 (which does not require you to "spell out all the columns") and then the IF blocks just run a standard insert and there is no conflict. If you want workarounds, there are plenty available. If you want to know why it works this way, or expect some kind of change, you'll have to contact Microsoft. – Aaron Bertrand Apr 16 at 15:24
  • @AaronBertrand yea, I had used something similar to Martin's where clause in my own scripts. I just found this behavior odd. Sometimes it really do be like that, I guess... – James Apr 16 at 15:34
  • 1
    This is an issue that only affects #temp tables - not permanent tables. A similar question is here dba.stackexchange.com/questions/153607/… – Martin Smith Apr 16 at 15:37