Create script with error handling

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 2005 Forums

Transact-SQL (2005)

Create script with error handling

Author

Topic

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2010-07-29 : 08:33:01

Hi, I would like to tell the user to choose correct database before updating with this script. So when the script is run it should only raise the error and not continue. But it seems like I can't place "GO" after the DROP PROCEDURE in the case below. How can I do to run everything between BEGIN-END only when the correct database is used?


USE [CHOOSE CORRECT DATABASE];
GO
IF @@ERROR=0
BEGIN 
    IF (EXISTS (SELECT name FROM sysobjects WHERE name = 'MyProcedureName' AND type = 'P')) DROP PROCEDURE MyProcedureName
    GO 
    CREATE procedure MyProcedureName @Id int as ....
END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-29 : 08:59:28

GO is the end of a batch so can't use it inside.
The other question, as an approach:
if (select db_name()) <> 'the_wanted_database_name'
begin
select 'here comes an error...'
end
else
begin
select 'here we go...'
end

No, you're never too old to Yak'n'Roll if you're too young to die.

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2010-07-29 : 09:11:22

webfred, thanks for trying to help me out but that will not work for me. I need to do below only if correct database is chosen because I don't want anybody to create the procedure in another database by mistake. That is why I need USE [CHOOSE CORRECT STATIC DATABASE] IF @@ERROR=0 ...DROP PROCEDURE...GO...CREATE Procedure...

I mean, I want to drop create...not alter procedure..so I need GO in between..

    IF (EXISTS (SELECT name FROM sysobjects WHERE name = 'MyProcedureName' AND type = 'P')) DROP PROCEDURE MyProcedureName
    GO 
    CREATE procedure MyProcedureName @Id int as ....
GO
    IF (EXISTS (SELECT name FROM sysobjects WHERE name = 'MyProcedureName2' AND type = 'P')) DROP PROCEDURE MyProcedureName2
    GO 
    CREATE procedure MyProcedureName2 @Id int as ....

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2010-07-29 : 09:31:45

Something like this would be nice if it worked.


USE [CHOOSE CORRECT DATABASE];
GO
IF @@ERROR<>0 
	GOTO ErrorMessage
ELSE 
	GOTO RunScript

ErrorMessage: 
	print 'Please replace "CHOOSE CORRECT DATABASE" with the correct database'
	GOTO Stop

RunScript: 
	IF (EXISTS (SELECT name FROM sysobjects WHERE name = 'MyProcedureName ' AND type = 'P')) 
	   DROP PROCEDURE MyProcedureName 
 
	GO
 
	CREATE procedure MyProcedureName 
		@id_id int
	as
        ...
Stop:
    print ''

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-29 : 09:32:28

I think what you want to do isn't possible with only T-SQL.
Maybe someone else has a good idea...

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-29 : 09:38:28

Your solution is creating the SP in the current database if [CHOOSE CORRECT STATIC DATABASE] isn't replaced with the correct database.

No, you're never too old to Yak'n'Roll if you're too young to die.

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2010-07-29 : 09:41:46

quote:
Originally posted by webfred

Your solution is creating the SP in the current database if [CHOOSE CORRECT STATIC DATABASE] isn't replaced with the correct database.

No, you're never too old to Yak'n'Roll if you're too young to die.

True, that is why I updated the message :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources