Hash table creation issue in Sql server

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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,

Answer 4

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,