conditionally running big blocks of SQL script

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)

conditionally running big blocks of SQL script

Author

Topic

BENISIZ
Starting Member

11 Posts

Posted - 2006-04-13 : 05:37:39

hi guys,
I'm facing a major problem with the way i want my SQL script to be executed on the server while i'm installing my SETUP program.

My SQL script basically, creates a database and right after that uses it to create/insert tables. My problem is that i want this script to be executed only if the databse i'm supposed to make doensn't exists on the DB server. Checking the existence of the database is not the problem though. A simple IF NOT EXISTS (select * from master.dbo.sysda ....) does the job.

The problem is the execution of the big BLOCK of SQL script based on this condition. I get erros nearly everywhere. I've used the BEGIN/END, WHILE command and all seem to fail.

could somebody plz tell, if there is a way to get around this problem.

respectfully yours,
Ben

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-04-13 : 06:26:58

It will be because of all the GO statements, if you don't want to run the rest of the script if the database already exists, you will need to break out of the script after the check..

BENISIZ
Starting Member

11 Posts

Posted - 2006-04-13 : 06:41:24

quote:
Originally posted by RickD

It will be because of all the GO statements, if you don't want to run the rest of the script if the database already exists, you will need to break out of the script after the check..

That is exactly the problem. How do i break out of the script when the database exists?! OR how do i resume the execution when the database not exists?!

Kristen
Test

22859 Posts

Posted - 2006-04-13 : 06:57:26

"How do i break out of the script when the database exists"

No easy way that I can think of. Your best bet would be two have two scripts, run the first script to check if the database exists and then have that run the second script if the database doesn't exist. You'd need a little application to do that I reckon - unless you can get a Return Code from OSQL, in which case you could do it in a Batch File.

Kristen

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-04-13 : 06:59:56

It's a bit of a pain as you'd need to get rid of your GO statements..

If you did, then you could just put a RETURN after the IF and the rest wouldn't run as in:


IF NOT EXISTS (select * from master.dbo.sysdatabases where name = 'master') 
BEGIN
	Print 'Create'
END
ELSE
BEGIN
	GOTO returnlabel
	Print 'Exists'
END

select * from master.dbo.sysdatabases

ReturnLabel:
print 'Finish'

BENISIZ
Starting Member

11 Posts

Posted - 2006-04-13 : 07:17:16

Thank u guys for the quick replies,

I really need to Exit from the same script i use to create my database. The reason for that are the limitations of InstallShield(the program i use to create my setup).
This might be a silly idea and i apoligize for that(i'm a noob), but is there any way to replace 'strings' within a SQL script. cause if that is possible it means , i can place '/*' in my script wherever/whenever i want to stop my script from being executed.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-04-13 : 07:38:47

Not sure what you mean by replace strings? The problem is that GO donates the end of the current batch of SQL and anything after that is a new batch..

BENISIZ
Starting Member

11 Posts

Posted - 2006-04-13 : 07:52:41

while reading some tutorials i came across this :
SELECT REPLACE('replaceme','replaceme','/*').....I though i might be able to put '/*' somewhere in the script to avoid the rest of it from getting executed, but it was a silly idea, i know

quote:
Originally posted by RickD

Not sure what you mean by replace strings? The problem is that GO donates the end of the current batch of SQL and anything after that is a new batch..

Kristen
Test

22859 Posts

Posted - 2006-04-13 : 08:12:55

I think the only thing you could do is to try to execute your "main script" from somewhere else if the database does not exist. Maybe you could have your main script as XML or somesuch that you could walk round in a loop and run "each bit" [but only if the Database Exists test failed]. Dunno if you can do something like that in InstallShield though.

Kristen

BENISIZ
Starting Member

11 Posts

Posted - 2006-04-13 : 08:26:00

It think it's possible but it's not the best way to do it.

quote:
Originally posted by Kristen

I think the only thing you could do is to try to execute your "main script" from somewhere else if the database does not exist. Maybe you could have your main script as XML or somesuch that you could walk round in a loop and run "each bit" [but only if the Database Exists test failed]. Dunno if you can do something like that in InstallShield though.

Kristen

barmalej
Starting Member

40 Posts

Posted - 2006-04-13 : 09:28:37

Hi,
I add one more silly idea, I even once tried it.
If database exists (or in fact any other condition you want to check) then create new dummy database DUMMYDATABASE and write after its creation
USE DUMMYDATABASE
Then all your next SQL statements will be against new database. Perhaps you will get lots of errors but else this idea would not be silly ;-)
(Drop new database in the end of processing.)

Optionally you may try to use Tempdb for the same purpose.

Gennadi

BENISIZ
Starting Member

11 Posts

Posted - 2006-04-13 : 09:50:57

That might work, but the problem is that i can't get rid of al the GO statements cause then "InstallShield" wouldn't be able to execute the SQL scripts. I've found a VBScript that makes a conenction with the DB server based on the values that it gets from my installer. Using that VBS i can return a value or change a value of a property that later is used as a condition for the execution of my SQL script.

anyway, i wanna thank u all for u're kind aswers.
I think the best way is to use that VBS.

quote:
Originally posted by barmalej

Hi,
I add one more silly idea, I even once tried it.
If database exists (or in fact any other condition you want to check) then create new dummy database DUMMYDATABASE and write after its creation
USE DUMMYDATABASE
Then all your next SQL statements will be against new database. Perhaps you will get lots of errors but else this idea would not be silly ;-)
(Drop new database in the end of processing.)

Optionally you may try to use Tempdb for the same purpose.

Gennadi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources