USE database, but how can it check that database exists before using it

Question

Post reply

USE database, but how can it check that database exists before using it

Dean Jones-454305

SSCarpal Tunnel

Points: 4804
More actions
September 15, 2009 at 3:51 am

#72534

Hello Guys,
I was just wondering if someone can help here, I have the code below which runs as part of a batch, but I need to make sure that the IF statement doesn’t get executed if the database does not exists.
I know one possible solution is to wrap this around T-SQL and make it dynamic, but I was wondering if there are other ways of doing this.
Thanks in advance.
IF EXISTS(SELECT * FROM sysdatabases where name = 'PersonProfile')
EXEC sp_executesql N'USE PersonProfile'
IF db_id('PersonProfile') is not NULL
BEGIN
USE PersonProfile
IF NOT EXISTS(SELECT * FROM sysusers where name = 'loginname')
exec sp_grantdbaccess N'loginname',N'loginname'
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
GRANT EXECUTE ON XXX TO loginname
update table set BillCode = 0 where ID <= 2000 END
[/code]

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

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

Dean Jones-454305 SSCarpal Tunnel Points: 4804 More actions · Answer 1

Dean Jones-454305

SSCarpal Tunnel

Points: 4804

September 15, 2009 at 8:26 am

#1053808

Any ideas guys ?

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 2

select from sys.databases?

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Dean Jones-454305 SSCarpal Tunnel Points: 4804 More actions · Answer 3

Steve Jones - Editor (9/15/2009)
select from sys.databases?

That doesnt work, even beforeit checks the sys.databases the compiler already attempts to connect to a database that might not exists, thats where the problem is.

Sergiy SSC Guru Points: 110192 More actions · Answer 4

Put your code into system procedure on master database and execute it like this:

IF EXISTS(SELECT * FROM sysdatabases where name = 'PersonProfile')

EXEC PersonProfile.dbo.sp_MyGrantPermissionProc

If it does not work like this make it dynamic.

_____________
Code for TallyGenerator