I CREATED A DATABASE--------

create database temporarytables

use temporarytables

--I CREATED TEMPORARY TABLE INSIDE A STORED PROCEDURE

create proc sp_testtemptable

as

begin

create table #temptable(tempno int)

end

THIS TIME I RUN THE SELECT * FROM #TEMPTABLE, OUTSIDE THE PROCEDURE, AND THE ERROR OCCURS THAT THE TABLE DOES NOT EXIST, which is fine as i read that temporary tables created in procedures are deleted when the procedure completes.

---------------------------------------------------------------------------------------------------

--NOW, I CREATED TEMPORARY TABLE1 INSIDE A STORED PROCEDURE & USED A SELECT QUERY ALSO

create proc sp_testtemptable1

as

begin

create table #temptable1(tempno1 int)

select * from #temptable1

end

WHEN I EXECUTE THE PROCEDURE, IT DISPLAYS THE TEMPTABLE1 TABLE.

BUT WHEN I RUN THE SELECT * FROM #TEMTABLE1, OUTSIDE THE PROCEDURE, THEN ALSO IT DISPLAYED THE TABLE. THE ERROR DID NOT OCCUR? MEANS THE TABLE WAS SUCCESSFULLY DISPLAYED BY THE SELECT QUERY. WHY SO?

ACCORDING TO THE CONCEPT THAT WHEN PROCEDURE COMPLETES, TEMPORARY TABLES ARE DESTROYED. SO THIS TIME ALSO ,THE TABLE SHOULD HAVE BEEN DESTROYED AND IT SHOULD SHOW ERROR INSTEAD OF DISPLAYING THE TABLE.

---------------------------------------------------------------------------------------------------