Basic logic in a transaction - 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

General SQL Server Forums

New to SQL Server Programming

Basic logic in a transaction

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2010-09-27 : 14:35:36

My little query wants to be a stored procedure. I have more to add to this but I need my logic to work first. What do I need to change here:


BEGIN TRANSACTION

DECLARE @email VARCHAR(50)
SET @email='craig@craiggreenwood.com'
DECLARE @token VARCHAR(16)
SET @token='vejt54dbkii2n62'

UPDATE pdp..tokens_67732 
SET completed = 'N', sent= 'N'
WHERE tid = (SELECT tid 
		    FROM pdp..tokens_67732
		    WHERE email=@email
		    AND token=@token)
IF @@ROWCOUNT = 1
	COMMIT TRANSACTION
	PRINT 'Survey for ' + @email + ' has been reset.'
ELSE
	ROLLBACK
	PRINT 'Token ' + @token + ' does not exist under the email address ' + @email + '';

Right now it fails on the ELSE statement. The update statement works fine so don't focus on that. The logic is the problem. Why can't it do two things (commit and print OR rollback and print)?

Craig Greenwood

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-27 : 14:39:06

I think you just need some begin and ends

IF @@ROWCOUNT = 1
BEGIN
COMMIT TRANSACTION
PRINT 'Survey for ' + @email + ' has been reset.'
END
ELSE
BEGIN
ROLLBACK
PRINT 'Token ' + @token + ' does not exist under the email address ' + @email + '';
END

Jim

Everyday I learn something that somebody else already knew

craigwg
Posting Yak Master

154 Posts

Posted - 2010-09-27 : 14:45:48

Hot dog! That seemed to do it. Thanks!

Craig Greenwood

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-27 : 15:33:53

You're Welcome!

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources