Using XACT_STATE in a CATCH block

The XACT_STATE function can be used in a CATCH block to decide whether to commit or roll back a transaction.

In the example below, the UPDATE statement raises an error and control is passed to the CATCH block. In the CATCH block, XACT_STATE is used to check that the transaction can be rolled back. In this case, the INSERT statement before the failed UPDATE statement is rolled back and CountyTown remains an empty table.

/* If XACT_ABORT is ON, an error will cause the entire
transaction to be terminated and rolled back. It is
recommended to set XACT_ABORT ON so that command timeouts
won't leave the transaction open. For the purposes of this
post XACT_ABORT is set to OFF to show how XACT_STATE can
be used to commit part of a transaction. */

SET XACT_ABORT OFF

IF OBJECT_ID('CountyTown','U') IS NOT NULL
	DROP TABLE CountyTown;
GO

CREATE TABLE CountyTown
(
	[County] NVARCHAR(10)
	,[Town] NVARCHAR(10)
);
GO

BEGIN TRY
	BEGIN TRANSACTION;
	
	/* succeeds */
	INSERT INTO CountyTown VALUES
	(N'Tipperary',N'Clonmel')
	,(N'Clare',N'Ennis');		
	
	/* fails - error occurs */
	UPDATE CountyTown
	SET [County] = N'This string is too long';	
	
	/* does not execute as control is
	passed to CATCH block */
	INSERT INTO CountyTown VALUES
	(N'Kerry',N'Tralee');
	
	/* would commit the transaction if 
	an error did not occur */
	COMMIT TRANSACTION;
END TRY
BEGIN CATCH
	/* rolls back the transaction
	if this is possible (i.e. if XACT_STATE is
	not equal to 0) */
	IF XACT_STATE()  0
	BEGIN
		PRINT N'Error encountered'
		PRINT N'Error Msg: '+ERROR_MESSAGE()+N' Line: '
                      +CAST(ERROR_LINE() AS NVARCHAR(10))
		PRINT N'Rolling back transaction'
		ROLLBACK TRANSACTION;
	END
END CATCH
GO

In the following example, XACT_STATE is used to commit the statements that executed before the error occurred; that is, the first INSERT statement is committed and the table CountyTown will contain 2 rows:

County	Town
Tipperary	Clonmel
Clare	Ennis

/* If XACT_ABORT is ON, an error will cause the entire
transaction to be terminated and rolled back. It is
recommended to set XACT_ABORT ON so that command timeouts
won't leave the transaction open. For the purposes of this
post XACT_ABORT is set to OFF to show how XACT_STATE can
be used to commit part of a transaction. */

SET XACT_ABORT OFF

IF OBJECT_ID('CountyTown','U') IS NOT NULL
	DROP TABLE CountyTown;
GO

CREATE TABLE CountyTown
(
	[County] NVARCHAR(10)
	,[Town] NVARCHAR(10)
);
GO

BEGIN TRY
	BEGIN TRANSACTION;
	
	/* succeeds */
	INSERT INTO CountyTown VALUES
	(N'Tipperary',N'Clonmel')
	,(N'Clare',N'Ennis');		
	
	/* fails - error occurs */
	UPDATE CountyTown
	SET [County] = N'This string is too long';	
	
	/* does not execute as control is
	passed to CATCH block */
	INSERT INTO CountyTown VALUES
	(N'Kerry',N'Tralee');
	
	/* would commit the transaction if 
	an error did not occur */
	COMMIT TRANSACTION;
END TRY
BEGIN CATCH
	/* rolls back transaction
	if transaction is uncommittable */
	IF XACT_STATE() = -1
	BEGIN
		PRINT N'Error encountered'
		PRINT N'Error Msg: '+ERROR_MESSAGE()+N' Line: '
                      +CAST(ERROR_LINE() AS NVARCHAR(10))
		PRINT N'Rolling back transaction'
		ROLLBACK TRANSACTION;
	END
		
	/* commits the transaction
	if transaction is committable */
	IF XACT_STATE() = 1
	BEGIN
		PRINT N'Error encountered'
		PRINT N'Error Msg: '+ERROR_MESSAGE()+N' Line: '
                      +CAST(ERROR_LINE() AS NVARCHAR(10))
		PRINT N'Committing transaction'
		COMMIT TRANSACTION;
	END
END CATCH
GO

Filter on Rank in Tableau

Suppose you have a list of products and you have been asked to display product X and also the 3 products above X and the 2 products below X as ranked by sales. The solution allows the user to enter product X e.g. if the user enters “Milk”:

And if the user enters “Broccoli”:

And if the user enters “Yoghurt”:

The trick is to first create a field called [Rank by Sales] which ranks the products by sales using the formula RANK(SUM([Sales Amount]),’desc’). Then create a field called [Selected Product Rank] which is non-zero only for the selected product using the formula:

IF LOOKUP(MIN([Product]),0) = [p.Selected Product] THEN
    [Rank by Sales]
ELSE
   0
END

The LOOKUP() function allows us to mix the aggregrates ([Rank by Sales]) and non-aggregates ([Product]). Finally create a field called [Filter for 3 above and 2 below] which evaluates to TRUE for the selected product and the 3 products ranked above and the 2 products ranked below the selected product:

(WINDOW_MAX([Selected Product Rank])>4
AND
[Rank by Sales]>=(WINDOW_MAX([Selected Product Rank])-3)
AND
[Rank by Sales]<=(WINDOW_MAX([Selected Product Rank])+2))
OR
(WINDOW_MAX([Selected Product Rank])<=4
AND
[Rank by Sales]<=6)

The expression WINDOW_MAX([Selected Product Rank]) returns the rank of the selected product. Make sure that all the table calculations are computed using Table(Down).