Good summary and followup to the prior article http://www.sqlservercentral.com/articles/FMTONLY/64130/ "How SSIS and Other Tools Obtain Metadata". Based on the comments there, two additional points to make:
1 - Note that PRINT does nothing with FMTONLY on, just like RAISERROR
2 - Note that any variable assignment while FMTONLY is ON takes effect as soon as it is set OFF. This matters when you are trying to tell whether your proc was called with FMTONLY ON, before setting it OFF, and then doing something different based on that.
For example, you might raise an error if an ID parameter is not a valid entry in a table, which will always be true when SSIS is testing your metadata, so you need to check how your proc was called before actually raising the error.
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)