Incorrect syntax near drop table

问题:

I want to write the following SQL query, however there are incorrect syntax errors with drop table @temp and create table @temp, why is it that? (Using SQL Server 2015)

DECLARE @temp NVARCHAR(10)
SET @temp='sth'
IF OBJECT_ID(@temp, 'U') IS NOT NULL 
DROP TABLE  @temp
CREATE TABLE @temp

回答1:

To answer the question WHY, it is a syntactic convention from days long past.
The DROP statement goes like this:

    DROP TABLE MyTable

and not like this:

    DROP TABLE 'MyTable'

MyTable is an identifier, and identifiers in SQL are not quoted. And because it uses no quotes, it does not accept variables.

Also compare with a WHERE clause:

    WHERE MyCol = 'abc'
    -- MyCol is not quoted because it is an identifier.
    -- 'abc' is quoted because it is a value, and so it can be replaced by a variable.

回答2:

Try This

IF OBJECT_ID('TEMPDB..#TEMP) IS NOT NULL 
    DROP TABLE #TEMP;