SQL Command Name is Database Name; Use Dynamic SQL?

Question

I have a database with the name "Union". I am trying execute SQL for this database in the MAINT table but since 'union' is a SQL command it is throwing errors. I can get the query to run when executing from Union database. Would dynamic SQL be able to fix my problem or should I change the database name?

I keep getting incorrect syntax near keyword 'UNION' here is what I have so far,

     DECLARE @sql varchar(max)
DECLARE @Database varchar(5)
Set @Database = 'UNION'


SELECT @sql = 'SELECT '+@Database+' as ''Database'', '+@Database+'.hsi.useraccount.username  as ''User Name'', 
'+@Database+'.hsi.useraccount.realname as ''Real Name''
FROM '+@Database+'.hsi.useraccount
WHERE '+@Database+'.hsi.useraccount.username NOT LIKE ''%deactivated%'' and '+@Database+'.hsi.useraccount.username not like ''%administrator'' and '+@Database+'.hsi.useraccount.username not like ''%internal%'''

execute(@sql)

@Siyual: Might as well make that comment an answer and get proper credit for it. — Joe Stefanelli
– Joe Stefanelli, Commented Nov 2, 2015 at 20:05

EMUEVIL · Accepted Answer · 2015-11-02 20:14:08Z

3

Add [] brackets around Schema names.

SELECT @sql = REPLACE('SELECT [@Database] as ''Database'', [@Database].hsi.useraccount.username  as ''User Name'', 
[@Database].hsi.useraccount.realname as ''Real Name''
FROM [@Database].hsi.useraccount
WHERE [@Database].hsi.useraccount.username NOT LIKE ''%deactivated%'' and [@Database].hsi.useraccount.username not like ''%administrator'' and [@Database].hsi.useraccount.username not like ''%internal%'''
,'@Database',@Database)

As long as the text "@Database" text doesn't appear anywhere else in your select statement, just throw it into a REPLACE() function and avoid all that embedded quote syntax and string concatenation headache.

edited Nov 2, 2015 at 20:14

answered Nov 2, 2015 at 20:07

EMUEVIL

5124 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TheGameiswar · Accepted Answer · 2015-11-03 02:53:42Z

0

You also can use quotename instead of manually entering square brackets

declare @db nvarchar(100)
set @db='performance'

declare  @sql nvarchar(max)
set @sql='select * from '+QUOTENAME(@db)+'.'+quotename('dbo')+'.'+QUOTENAME('orders')
print @sql

exec(@sql)

answered Nov 3, 2015 at 2:53

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

Collectives™ on Stack Overflow

SQL Command Name is Database Name; Use Dynamic SQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related