Repeatedly creating and dropping temp table of the same name

Question

I understand what the debugger is doing but I don't really agree with it, as the following should just surely work :

IF OBJECT_ID('TEMPDB..#extract') IS NOT NULL DROP TABLE #extract;
CREATE TABLE #extract(
    x                   VARCHAR(100),
    NumX_Rank           NUMERIC(18,2),  
    NumG_Rank           NUMERIC(18,2)
    );

IF OBJECT_ID('TEMPDB..#extract') IS NOT NULL DROP TABLE #extract;
CREATE TABLE #extract(
    x                   VARCHAR(100),
    NumX_Rank           NUMERIC(18,2),  
    NumG_Rank           NUMERIC(18,2)
    );

Error message is as follows:

Msg 2714, Level 16, State 1, Line 15 There is already an object named '#extract' in the database.

Please note - I have variables declared that I require throughout the script, i.e. the script is part of a stored procedure and the variables should have a scope across the whole proc.

Answer 1

Just Use Go Statement, Which Ensures that Scope of Current Batch Execution

:setvar TotalScope 10
IF OBJECT_ID('tempdb..#extract') IS NOT NULL

 DROP TABLE #extract;
CREATE TABLE #extract(
    x                   VARCHAR(100),
    NumX_Rank           NUMERIC(18,2),  
    NumG_Rank           NUMERIC(18,2)
    );


//what ever Operation what you want to Perform on `#extract`

 GO


// So here `#extract` is not available then You can Create New One Now
select $(TotalScope)


 IF OBJECT_ID('tempdb..#extract') IS NOT NULL

 DROP TABLE #extract;
CREATE TABLE #extract(
    x                   VARCHAR(100),
    NumX_Rank           NUMERIC(18,2),  
    NumG_Rank           NUMERIC(18,2)
    );


   // Again Do what ever Operation what you want to Perform on `#extract`


 GO

  // So here `#extract` is not available then You can Create New One Now

EDIT

You cann't use This type of @TotalScope variables in Between Batch Execution But You need to ON SQL CMD MODE and then You can Use As I Updated Above code

For Reference

Note: As Suggested By alroc Instead Of Creating New Table One it will be better if You Truncate table