The Amazing FMTONLY Trick

I created a stored procedure to generate data for a report which involved several temp tables. To check the intermediate results, I thought I’d output the results of the various temp tables only during development, but skip the temp tables in production:

DECLARE @dev BIT = 0;    -- set to 1 during development
IF @dev = 1 SELECT * from @temp

This works fine in SSMS.

But trying to set the SP as report DataSource in Report Designer (.rdl Editor), VS offered the columns of the first SELECT statement, ignoring the value of @dev.

As you may know, when you define a data source in a VS IDE tool, the IDE invokes the SQL statement using the SET FMTONLY ON setting to retrieve information on the columns of the data source.

One feature of the FMTONLY mode is how IF statements are processed: BOTH the IF and the ELSE branch are analyzed independent of the result of the IF condition. This explains why the IF … SELECT always returns the column data of every SELECT statement in the SP.

I searched and found this article on SQLServerCentral on how to output only specific data sets in FMTONLY mode. What I did not know was that you can set FMTONLY anywhere inside a T-SQL procedure!

My variant of the code in the referenced article looks like this:

– Detect whether FMTONLY is set (only then the IF(1=0) will be executed):

DECLARE @fmtonlyON BIT = 0;
IF (1=0) SET @fmtonlyON = 1;

– Create and SELECT temp tables as you like, IF statements will be handled correctly

– Reset FMTONLY where required

IF @fmtonlyON = 1 SET FMTONLY ON;

A comment on the article also mentions that this method allows #Temp tables in SPs without raising the error message

Msg 208, Level 16, State 0, Line {line}
Invalid object name '#TempTable'.

*Yes, the title of this blog was inspired by this great video. Enjoy😉

4 Responses to The Amazing FMTONLY Trick

  1. Gerald Britton says:

    Have you found any official documentation that describes how FMTONLY ON handles conditional branches?

  2. What is the purpose of this statement “IF @fmtonlyON = 1 SET FMTONLY ON;”? I seems that it is meant to turn fmtonly back on if the sproc is running in and fmtonly on scenario. But the “if” won’t evaluate and fmtonly will be set to on regardless of the value of @fmtonlyon. Wouldn’t “If(1=0) set fmtonly on;” also work? It seems like @fmtonly is unnecessary. Not trying to be cleaver here… just trying to understand. Thanks for posting this information.

    • devio says:

      @fmtonlyON stores whether FMTONLY was *initially* ON. If the SP resets it to OFF, the condition “IF (1=0)” later in the code would not trigger. If the SP never changes the FMTONLY state, then @fmtonlyON is not necessary. Good point

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: