CODEWITHSUNDEEP
sql-servert-sql
hln
hln

Reputation: 1106

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

Upvotes: 0

Views: 3719

Answers (2)

Jacky Montevirgen
Jacky Montevirgen

Reputation: 317

Try This 

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

Upvotes: 0

Peter B
Peter B

Reputation: 24147

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.

Upvotes: 1

Related Questions