Hash table creation issue in Sql server

yashwin g shetty 40 Reputation points
2024-05-30T09:15:06.0866667+00:00

I have created hash table in outer procedure with 2 columns and trying to create another hash table with same name but 3 columns. Now if i try to insert into hash table in inner procedure i get error saying not able to access third column (which was not created in outer procedure) but i am expecting table creation error.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,067 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 103.5K Reputation points MVP
    2024-05-30T21:45:10.4233333+00:00

    This is very confusing.

    In a nutshell, you can create temp tables with the same name if you create them in different scopes. If you create #temp in both inner_sp and outer_sp, the #temp in inner_sp will hide the one created in outer_sp, but not until #temp in inner_sp has been created.

    This is confusing enough, but due to that SQL Server cache query plans, you can also get very inconsistent results. This runs:

    CREATE PROCEDURE inner_sp AS
       CREATE TABLE #temp(b int NOT NULL)
       SELECT b FROM #temp
    go
    EXEC inner_sp
    go
    CREATE PROCEDURE outer_sp AS
       CREATE TABLE #temp(a int NOT NULL)
       SELECT a FROM #temp
       EXEC inner_sp
    go
    EXEC outer_sp
    

    This works, because when outer_sp calls inner_sp, there is already a plan for inner_sp in the cache.

    But if you now run:

    ALTER PROCEDURE inner_sp AS
       CREATE TABLE #temp(b int NOT NULL)
       SELECT b FROM #temp
    go
    EXEC outer_sp
    

    You get this error:

    Msg 207, Level 16, State 1, Procedure inner_sp, Line 3 [Batch Start Line 127] Invalid column name 'b'.

    Because inner_sp was recompiled, the plan is no longer in the cache. When outer_sp calls inner_sp, the procedure has to be compiled. But it is compiled against the definition from outer_sp.

    To avoid this confusion, don't use names like #temp etc, but give your temp tables distinct names.


3 additional answers

Sort by: Most helpful
  1. 博雄 胡 90 Reputation points
    2024-05-31T05:08:21.4866667+00:00

    Everyone has explained why your SQL will report an error. But if you want, you can change it to the following equivalent SQL without any errors:

    If OBJECT_ID('Proc1') is not null
    Drop Proc Proc1
    Go
    If OBJECT_ID('Proc2') is not null
    Drop Proc Proc2
    Go
    If OBJECT_ID('tempdb..#temp1') is not null 
    Drop table #temp1
    Go
    
    
    Create Proc Proc1
    as
    
    select * into #temp1 from (
    select a=1,b=2
    )T
    Go
    
    Create Proc Proc2
    as
    
    create table #temp1(Column1 int)
    
    Exec Proc1
    
    insert into #temp1(Column1) select 1
    
    Go
    
    Exec Proc2
    
    
    1 person found this answer helpful.
    0 comments No comments

  2. Zahid Butt 556 Reputation points
    2024-05-30T13:34:57.21+00:00

    Hi @yashwin g shetty ,

    If you are talking about tables with hash sign (#,##) in their name: for single # sign scope is limited to same session in which its created, while table with ## in its name can be used globally until the session creating the table is open. Further you may look into below link:

    https://www.mssqltips.com/sqlservertip/7982/sql-server-temporary-tables-local-global/

    If you are talking about tables using hashbytes() for encryption then you may please look into below links:

    https://learn.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver16

    https://stackoverflow.com/questions/10952213/generate-unique-hash-for-a-field-in-sql-server

    Hope it helps you, if not please give more detail as Olaf said.

    Regards,

    0 comments No comments

  3. Zahid Butt 556 Reputation points
    2024-05-30T17:43:26.0033333+00:00

    Hi,

    Please create first sp named Proc1 like below & create 2nd SP with same code as you posted above:

    Create Proc Proc1

    as

    Create table ##temp1(Column1 int, Column2 int)

    Insert into ##temp1(Column1, Column2)

    Select 1, 2

    Go

    Create Proc Proc2 with code as you posted above.

    Exec Proc2

    select * from ##temp1

    It is working fine now.

    Note: You can not take output from #temp1 because its dropped as sp execution completes.

    Regards,

    0 comments No comments