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". Test procedure. CREATE procedure [dbo].[testtemptable] as begin create table #testtemptable ( a varchar(10) null ) end
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.
Read More.. [32134 clicks]
Published under: SQL Server Interview Questions · · · ·