syntax error.

Question

syntax error.

bluepaperbag

SSCrazy

Points: 2055
More actions
October 10, 2006 at 12:50 pm

#173168

declare @townID varchar(25)
set @townID = '''00005'''
print @townID
if exists (select * from sysobjects where xtype= 'u' and name = @townID)
drop table @townID
why am i getting a syntax error.?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

You cannot reference a variable value as part of a DDL statement. In order to accomplish what you are attempting, you must use Dynamic SQL. Using your example,

declare @townID varchar(25),

@sql varchar(1000)

set @townID = '''00005'''

print @townID

if exists (select * from sysobjects where xtype= 'u' and name = @townID)

begin

set @sql = 'drop table ' + @townID

exec (@sql)

end

http://www.sommarskog.se/dynamic_sql.html

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

bluepaperbag SSCrazy Points: 2055 More actions · Answer 2

declare @townID varchar(25),

@sql varchar(1000)

set @townID = '''00003'''

if exists (select * from sysobjects where xtype= 'u' and name = @townID)

print 'exists'

else

print 'don''t exists'

its printing 'dont exists'. it should print exists as the table exists in the DB

what's wrong with it.?

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

It's the triple quotes in the variable assignment that are the most recent problem...

Try this...

declare @townID varchar(25),

@sql varchar(1000)

set @townID = '00003' --<<THIS IS WHERE THE PROBLEM WAS

if exists (select * from sysobjects where xtype= 'u' and name = @townID)

print 'exists'

else

print 'don''t exists'

Now, instead of messing around with System Tables, let's do it the right way (the dynamic SQL has been commented out for safety but YOU can uncomment it when you're ready)...

DECLARE @TownID VARCHAR(25)

DECLARE @SQL VARCHAR(8000)

SET @TownID = '00003'

IF OBJECT_ID(@TownID) IS NOT NULL

AND OBJECTPROPERTY(OBJECT_ID(@TownID),'IsTable') = 1

BEGIN

PRINT 'Exists'

SET @SQL = 'DROP TABLE '+@TownID

PRINT @SQL --Comment this line out when you uncomment the EXEC

-- EXEC @SQL --Commented out for safety

END

ELSE

BEGIN

PRINT 'NOT Exists'

END

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bluepaperbag SSCrazy Points: 2055 More actions · Answer 4

Jeff Moden what is the problem with sysobjects

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

There's really no physical problem to keep you from using it... However, Microsoft and a bunch of folks on this forum have indicated that in order to guarantee that your code will work in the future (post service pack, new release, whatever), you need to stay away from sysobjects because Microsoft could change it at ANY time. Most equate calls to system tables at the same level as using undocumented sp/xp's that could also go away at the drop of a hat. The system functions make a nice clean way to get around that. You could also use the Information_Schema views but I just like the functions better... seems cleaner.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)