problem w/ dynamic database dropping via DDL 1

[mirirom]

hi,

can anyone explain why this is returning an error when executing?

USE master
DECLARE @dbname sysname
SET @dbname = '234_56Test'
EXECUTE
('IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = ' + @dbname + ')
	DROP DATABASE ' + @dbname)
GO

the returned error is
Line 1: Incorrect syntax near '_56Test'.
which is referring to @dbname after 'name = ' + ...

any advice is appreciated. thanks in advance!

..:: mirirom ::..

 

[ClaireHCM]

DECLARE @dbname sysname
SET @dbname = '234_56Test'
Print
('IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = ''' + @dbname + ''')
DROP DATABASE [' + @dbname+']'
)

 

[mirirom]

excellent! thx for the quick reply w/working solution. i'm missing the logic as to why this works though -- just assumed the single quotes were enough for the string data. can you provide a quickie explanation? thx again...

..:: mirirom ::..

 

[ClaireHCM]

oops, I made a mistake, please replace
Print
('IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = ''' + @dbname + ''')
DROP DATABASE [' + @dbname+']'
)
with
Exec
('IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = ''' + @dbname + ''')
DROP DATABASE [' + @dbname+']'
)

Print command is very useful for troubleshooting, you could print out your original query, and copy -- paste in query analyzer -- execute the query, the error msg would tell you what's going on in your old query.

Programming language is so sensitive, even one sinle quote would break the whole set.

 

[mirirom]

ok, i see why the single quotes need to be embedded into the EXEC string litteral, but what's the signifigance of the square brackets in the DROP clause? does this have something to do with the difference of passing string data and the use of database object identifiers?

thx again.

..:: mirirom ::..

 

[maswien]

That makes sure the string for @dbname is treated as a
object name rather than multiparts if there are spaces in
the @dbname

 

[mirirom]

right, but in this forced example there are no spaces; the variable's string contents are contiguous but the statement still returns an error.

i also tested the following, which works(!) in lieu of the square brackets.

...DROP DATABASE N' + @dbname) /*end EXEC*/
GO

this is interesting because an underscore *is* part of the ASCII character set. i thought this might have something to do with the syntax error being returned (which only indicated *parts* of the variable's string contents: before or after the underscore).

weird.

..:: mirirom ::..