Visual Studio data access does not pickup return columns

I have noticed an interesting error with some stored procedures where Entity Framework or C# DataSets are unable to detect what the return data type will be. It appears to be due to the fact that when the stored procedure is added, the parameters are sent in as nulls, therefore the procedure won't execute properly and no return is generated.

After some searching I have found that by adding the following line to the start of the stored procedure, Visual Studio will then pickup the correct return type:

IF (1=0) BEGIN SET FMTONLY OFF END

The FMTONLY option is set to off by default, which allows stored procedures to return result sets as normal when they run. When Visual Studio runs a stored procedure for the first time (to get all input/output column names/types) it runs with FMTONLY set to on which basically allows it to parse the procedure for all columns (without actually sending any data in and running the logic). However because the actual logic of the stored procedure is not being run, temp tables and other advanced SQL logic used to return data are not run which means there are no return types being generated (so Visual Studio will not pick up the return columns).

Because FMTONLY is originally set to on, the logical if statement "IF (1=0)" is ignored, and FMTONLY turns itself off. Which means the "IF (1=0) BEGIN SET FMTONLY OFF END" fix is ignored during normal calls to the procedure but executed when Visual Studio and other scrapers access the procedure.