Insert/Update Etiqutes - 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)

Insert/Update Etiqutes

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-06-26 : 13:32:39

Hi,

I am updating the database table and I use the following T-SQL to do the update. My question is that is this a good way to commit the update. Same goes for the Insert commands. I use the following pretty much as a template. How can I improve on this?

CREATE PROCEDURE [usp_UpdateArchive]

@ArchiveID int,
@Title varchar(200),
@Description text,
@Active bit

AS

-- Begin the transaction
BEGIN TRAN

UPDATE Archives
SET Title = @Title,Description = @Description, Active = @Active
WHERE ArchiveID = @ArchiveID

-- Commit the transaction
IF @@ERROR = 0
BEGIN
COMMIT TRAN
RETURN 1
END

-- Rollback the transaction
ELSE
BEGIN
ROLLBACK TRAN
RETURN -1
END

Mohammad Azam
www.azamsharp.net

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-06-26 : 13:42:44

I usually check for the existence of an error rather than the absence.

BEGIN TRAN 

UPDATE Archives 
SET Title = @Title,Description = @Description, Active = @Active 
WHERE ArchiveID = @ArchiveID 
IF @@ERROR <> 0
BEGIN
	GOTO EndTran
END

-- Do some other operations within transaction
UPDATE Archives
SET ...
IF @@ERROR <> 0
BEGIN
	GOTO EndTran
END

COMMIT TRAN
RETURN 1

EndTran:
IF @@TRANCOUNT > 0
BEGIN
	ROLLBACK TRAN
	RETURN -1
END

Nathan Skerl

Kristen
Test

22859 Posts

Posted - 2006-06-26 : 14:01:52

nathans: Really pedantic point, and I know how snippets of code posted here can beligh reality, but I would be bothered by your label "EndTran:" - to me it implies "The transaction ended" and insofar as it is vague about the outcome I would infer success.

We name our labels [for this outcome] "Process_ABORT" - so for an SProc called "Name_Save" that label would be "Name_Save_ABORT" - the capitals for ABORT are used too, for emphasis

</PedanticPoint> !

Kristen

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-06-26 : 14:30:56

Thanks for your reply!

Mohammad Azam
www.azamsharp.net

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-06-26 : 14:52:20

Kristen, yea, I see how the label could be interpreted either way... your "pedantic points" are always appreciated

Nathan Skerl

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-06-26 : 15:08:11

This might be a simple question. But is there any different between the

!= operator and the <> operator.

Are both used for NOT EQUAL TO.

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-06-26 : 15:12:58

quote:

Originally posted by nathans

I usually check for the existence of an error rather than the absence.

BEGIN TRAN 

UPDATE Archives 
SET Title = @Title,Description = @Description, Active = @Active 
WHERE ArchiveID = @ArchiveID 
IF @@ERROR <> 0
BEGIN
	GOTO EndTran
END

-- Do some other operations within transaction
UPDATE Archives
SET ...
IF @@ERROR <> 0
BEGIN
	GOTO EndTran
END

COMMIT TRAN
RETURN 1

EndTran:
IF @@TRANCOUNT > 0
BEGIN
	ROLLBACK TRAN
	RETURN -1
END

Nathan Skerl

I think the main purpose of using the GOTO is for clarity since the check for the transaction count can also be placed inside the
IF @@ERROR <> 0 block.

Mohammad Azam
www.azamsharp.net

X002548
Not Just a Number

15586 Posts

Posted - 2006-06-26 : 15:13:00

Yes there is a difference..the characters are not the same...

I use this for 99% of the time

http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx

Oh, functionaly? they are identical....I wonder if '<>' is ANSI and that the other is not....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources