December 17, 2014 at 1:13 am
Two wrinkles to be aware of - PRINT has no effect with FMTONLY ON, and SELECT = constant takes effect as soon as you SET FMTONLY OFF. So this works:
SET FMTONLY ON
DECLARE @X INT
SET @X = 0
SELECT @X = 1
IF ( @X = 0 ) BEGIN
SET FMTONLY OFF
PRINT 'was on'
END ELSE PRINT 'was off'
but this does not:
SET FMTONLY ON
DECLARE @X INT
SET @X = 0
SELECT @X = 1
PRINT @X
SET FMTONLY OFF
IF ( @X = 0 ) BEGIN
PRINT 'was on'
END ELSE PRINT 'was off'
December 23, 2014 at 1:50 am
Looks like the key insight is in http://www.sqlservercentral.com/articles/SSRS/119024/ "Dealing with the Evil of FMTONLY from SSRS": every possible conditional code branch is evaluated. Yes, that is right: every IF / ELSE branch.
So it really does not matter what we test, as long as it is something that normally would not be true:
SET FMTONLY ON
IF 1 = 0 BEGIN
SET FMTONLY OFF
PRINT 'was on'
END
Although he recommends checking that @@OPTIONS = NULL.
April 20, 2015 at 5:37 pm
Moreover, a simple SELECT into a variable without a table is optimized as equivalent to a SET, so in the following example, only the third variation returns the expected NULL:
DECLARE @SetOpt INT, @SelOpt INT, @SetSelOpt INT
Set FMTONLY ON;
SET @SetOpt = @@OPTIONS
SELECT @SelOpt = @@OPTIONS
SET @SetSelOpt = (SELECT @@OPTIONS)
Set FMTONLY OFF;
SELECT @SetOpt AS SetOpt, @SelOpt AS SelOpt, @SetSelOpt AS SetSelOpt
Therefore, for maximum readability and predictability, I now use @t.ovod-everett simple suggestion:
DECLARE @NullIfFmtOnly INT = (SELECT 1);
or if you need it to work in 2005 and earlier:
DECLARE @NullIfFmtOnly INT SET @NullIfFmtOnly = (SELECT 1)
May 16, 2019 at 9:20 pm
Sorry, I know I'm...11 years late to the discussion, but in the original example, why is the condition even necessary? Why not just set FMTONLY OFF every time?
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply