Force Execute SQL Task Failure Upon Proc Error

Question

Post reply

Force Execute SQL Task Failure Upon Proc Error

cdun2

SSCertifiable

Points: 7893
More actions
October 2, 2008 at 1:21 pm

#125041

If I have an Execute SQL Task that executes a SQL Server 2005 proc, how can I force an Execute SQL Task to fail if an error occurs in the proc? The following is an example of a proc I'm working with:
CREATE PROCEDURE [dbo].[ZipCodeTruncateLocationStagingTables]
(
@LocationDataCountOut Int = 1 OUT
)
AS
DECLARE @County2StagingCount int
DECLARE @CityStagingCount int
DECLARE @CityTypeCityStagingCount int
DECLARE @CityZipCodeStagingCount int
DECLARE @StateCountyCityZipWithCodesStagingCount int
DECLARE @ZipCodeStagingCount int
SET NOCOUNT ON
BEGIN TRY
TRUNCATE TABLE County2_Staging
SET @County2StagingCount = (SELECT COUNT(*) AS RecCount FROM dbo.County2_Staging)
TRUNCATE TABLE City_Staging
SET @CityStagingCount = (SELECT COUNT(*) AS RecCount FROM dbo.City_Staging)
TRUNCATE TABLE CityTypeCity_Staging
SET @CityTypeCityStagingCount = (SELECT COUNT(*) AS RecCount FROM dbo.CityTypeCity_Staging)
TRUNCATE TABLE CityZipCode_Staging
SET @CityZipCodeStagingCount = (SELECT COUNT(*) AS RecCount FROM dbo.CityZipCode_Staging)
TRUNCATE TABLE StateCountyCityZipWithCodes_Staging
SET @StateCountyCityZipWithCodesStagingCount = (SELECT COUNT(*) AS RecCount FROM dbo.StateCountyCityZipWithCodes_Staging)
TRUNCATE TABLE ZipCode_Staging
SET @ZipCodeStagingCount = (SELECT COUNT(*) AS RecCount FROM dbo.StateCountyCityZipWithCodes_Staging)
SET @LocationDataCountOut = @County2StagingCount + @CityStagingCount + @CityTypeCityStagingCount + @CityZipCodeStagingCount + @StateCountyCityZipWithCodesStagingCount + @ZipCodeStagingCount
SELECT @LocationDataCountOut AS LocationDataCountOut
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() ERNumber,
ERROR_SEVERITY() ErrorSeverity,
ERROR_STATE() ErrorState,
ERROR_PROCEDURE() ErrorProcedure,
ERROR_LINE() ErrorLine,
ERROR_MESSAGE() ErrorMessage
END CATCH
********************
In this proc, I can use @LocationDataCountOut in a package variable to work with in error handling, but what can I do if the proc goes into the catch block and returns an error record? If I do something like misspell the name of one of the truncated tables in the proc, the Execute SQL Task still succeeds after the task is executed.
Thank you for your help!
CSDunn

Viewing 4 posts - 1 through 3 (of 3 total)

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

ironic SSC Enthusiast Points: 129 More actions · Answer 1

What about raising the error?

Use RAISERROR to throw the error:

RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]

Jack Corbett SSC Guru Points: 184388 More actions · Answer 2

I believe that you can return any value other than 0.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

cdun2 SSCertifiable Points: 7893 More actions · Answer 3

cdun2

SSCertifiable

Points: 7893

October 3, 2008 at 7:47 am

#880464

Thanks for your help!

CSDunn