Syntax issue

Question

Syntax issue

Andre 425568

SSCrazy

Points: 2479
More actions
July 25, 2016 at 7:41 am

#320123

I am missing something silly and am stuck. Please help with any advice. If a table on db1 has certain values we need the job to fail, if there are no records with certain values the job needs to continue.
DECLARE @i INT
SELECT @i = count(*) FROM db1.dbo.Jobs where status in ('B','P')
print @i
BEGIN transaction
IF @i = 0
truncate table db2.dbo.table1
alter table db2.dbo.table1 rebuild
PRINT 'Success'
else
PRINT 'Failure'
end

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You're missing the begin and a different set of begin...end. You're also missing the commit transaction. You're also using the slow option.

BEGIN TRANSACTION;

IF EXISTS(SELECT * FROM db1.dbo.Jobs where status in ('B','P'))

BEGIN;

PRINT 'Failure';

END;

ELSE

BEGIN;

TRUNCATE TABLE db2.dbo.table1;

ALTER TABLE db2.dbo.table1 rebuild;

PRINT 'Success';

END;

COMMIT TRANSACTION;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

And missing any form of error handling.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Andre 425568 SSCrazy Points: 2479 More actions · Answer 3

Andre 425568

SSCrazy

Points: 2479

July 26, 2016 at 12:28 am

#1891964

Thanks Louis

Andre 425568 SSCrazy Points: 2479 More actions · Answer 4

Gail. I would like to get your ideas on error handling in this specific case, not worked much with it

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

http://sqlinthewild.co.za/index.php/2011/05/17/on-transactions-errors-and-rollbacks/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Andre 425568 SSCrazy Points: 2479 More actions · Answer 6

Will have a look at it. I saw a lot of BEGIN, ROLLBACK and COMMIT, don't worry those are part of all my standard code, just did not include that.

Will investigate further though just to make sure. I am very much into double checking data, this code though is for a maintenance plan, so it will be tested thoroughly before being implemented. The table that will be truncated does not contain critical data, just keeps data of jobs run, with their parameters so it's reasonably safe, even if not ideal, should something go wrong. Don't worry. I am a bit OCD on things not going wrong, so I will be careful.