CASE statement validates both Databases

Question

CASE statement validates both Databases

homebrew01

SSC Guru

Points: 55454
More actions
March 30, 2010 at 11:51 am

#139060

When testing. I get an error that I cannot access the production database, even though the case statement should only be accessing development. I guess it checks permissions even though I'm not actually doing a select against PROD ?
I want fld4 to be set to different values depending on which database I'm in.
select fld1, fld2, fld3,
CASE WHEN DB_NAME() not in
(select database_name from OurDatabases where active_ind = 'Y')
THEN Development.dbo.Function(@var)
ELSE PROD.dbo.Function(@var)
END as fld4
from MyTable

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

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

liteswitch SSCommitted Points: 1824 More actions · Answer 1

homebrew01 (3/30/2010)
When testing. I get an error that I cannot access the production database, even though the case statement should only be accessing development. I guess it checks permissions even though I'm not actually doing a select against PROD ?
I want fld4 to be set to different values depending on which database I'm in.
select fld1, fld2, fld3,
CASE WHEN DB_NAME() not in
(select database_name from OurDatabases where active_ind = 'Y')
THEN Development.dbo.Function(@var)
ELSE PROD.dbo.Function(@var)
END as fld4
from MyTable

Yes indeed, when the code is checked, it parses the entire query first for errors and to make sure all the objects exist and are accessible before running the query itself.

Paul White SSC Guru Points: 150457 More actions · Answer 2

Use a synonym, view, or dynamic SQL?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281324 More actions · Answer 3

And then it typically defaults to the first database when trying to do something like this.

As Paul suggested, dynamic SQL, SSIS package, nested stored procedures, synonyms or views could resolve the issue.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150457 More actions · Answer 4

Using a synonym would be my default choice here, since they were pretty much invented just for this type of scenario.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

homebrew01 SSC Guru Points: 55454 More actions · Answer 5

Thanks for the feedback. Would a synonym work with a function that is in another database as I'm trying to do in my first post ?

Paul White SSC Guru Points: 150457 More actions · Answer 6

homebrew01 (4/2/2010)
Thanks for the feedback. Would a synonym work with a function that is in another database as I'm trying to do in my first post ?

Sure would - give it a go!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi