IF EXISTS() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

IF EXISTS()

Author

Topic

sriksdave
Starting Member

5 Posts

Posted - 2007-05-07 : 15:52:15

Hello Guys
I need some quick help here
Iam trying to write a sql script and put a

USE DBNAME
IF EXISTS ( SELECT 1 from ......)
BEGIN
SQL SCRIPT
END

Even though the Select statement doest retrieve any data. The loop is still getting executed.
Can you tell me whey its doing that?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-07 : 15:58:25

We need to see your actual code as what you have posted is fine.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

sriksdave
Starting Member

5 Posts

Posted - 2007-05-07 : 17:27:47

[code]
IF EXISTS (SELECT name FROM master..sysdatabases WHERE name = 'DBNAME')
BEGIN

USE DBNAME

exec sp_addrolemember N'db_owner', N'SupportGrp'

END
[/code]

When i run the script in query Analyser it gives me
"Could not locate entry in sysdatabases for database 'DBNAME'. No entry found with that name."

why is it even running the inside script if the db doesnt exist?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-07 : 17:59:07

You can't do that. SQL Server is checking for the database in the USE prior to even running the IF. I believe you could do this instead:

EXEC DBNAME.dbo.sp...

Tara Kizer
http://weblogs.sqlteam.com/tarad/

mattyblah
Starting Member

49 Posts

Posted - 2007-05-07 : 18:12:35

USE is not T-SQL. It's a query analyzer command, I believe.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources