I have a stored procedure like below and have created a temporary table inside it and have not written any drop statement for this Temporary Table.
Now when i execute this procedure for first time it will create the temporary table. Now when i run this procedure for second time, will it execute successfully or it will throw and an error stating "Table already exists in database".
CREATE procedure [dbo].[testtemptable]
create table #testtemptable
a varchar(10) null
The stored procedure will execute without any error no matter how many times you run it.
The reason being, when you create a local temporary table, it stays alive for that session and once session is over the temporary table is automatically deleted. When you run a procedure the session begins and it creates the temporary table and when the execution of Stored procedure is completed, the session ends and the table gets deleted.
SQL Server Interview Questions · · · ·