Stored Procedure: There is already an object named '#columntable' in the database

Question

Please see the code below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE Test
AS
BEGIN
    begin
    select * into #dbreviews from dbreviews
    end

    drop table #dbreviews

    begin
    select * into #dbreviews from dbreviews
    end

END
GO

The error I get is:

There is already an object named '#dbreviews' in the database.

Questions like this: There is already an object named '#columntable' in the database are telling me this should be possible.

Answer 1

As per SQL Server specification, it is not allowed. Please refer to the documentation.

If more than one temporary table is created inside a single stored procedure or batch, they must have different names.

You are creating two temporary tables, with the same name #dbreviews. This is not allowed.