First Transaction - Part II - ThnX !

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)

First Transaction - Part II - ThnX !

Author

Topic

ank2go
Starting Member

6 Posts

Posted - 2004-06-24 : 17:50:04

Thanks for reading this post.

I figure if the table was created successful then there should not be any problems
with creating the index. Maybe I'm wrong.

Should this SP includes a error handler after a CREATE INdEX statement?

create procedure dbo.happy as
BEGIN
begin transaction

SELECT * INTO DBO.TEST01 FROM TEST

if @@error <> 0 goto err_handler

CREATE INDEX idx_test01_id ON dbo.test01 (id)

if @@error <> 0 goto err_handler --is this necessary?

CREATE INDEX idx_test01_perid ON dbo.test01 (perid)

if @@error <> 0 goto err_handler --is this necessary?

commit transaction
return (0)

err_handler:
rollback transaction
raiserror ('Failed Stored Procedure1.',16,1)
print @@error
return (99)

END

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-24 : 20:15:02

Well anything can fail. You could get a deadlock on the system tables or just hit a bug.
Why would you want to do this in an SP though?
Might be better to create the schema then run something that checks for the existance of the objects.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-24 : 20:18:36

This procedure worked for me. If you do have an error, it will roll everything back of course, including the create table. You will only be able to run this one time also, so why are you making a procedure out of it unless you have another procedure that runs first and drops the table. If the table is already created, the entire procedure will always fail.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

ank2go
Starting Member

6 Posts

Posted - 2004-06-24 : 20:27:01

Thanks for the replies nr and derrickleggett !

You're right, I'm suppose to drop it first and then
re-create the table.

Anyways, I guess I'm going way over my head here.

I'll just include the error catch after the index.

Thanks again !

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-24 : 21:22:28

Why???? If the table is created, you can assume the index is created also. It won't matter where you include the error handling, the whole procedure will be rolled back. You might want to consider putting the drop statement in there. What exactly are you trying to accomplish with this procedure though?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

ank2go
Starting Member

6 Posts

Posted - 2004-06-25 : 13:58:18

Hi derrickleggett,

Sorry for the confusion; I do have a drop statement right
before I create the table in the same stored procedure.
I know what you mean if I don't drop it,it will always rollback.

Thanks !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources