I recently joined one of the project in my team. They use ASP.NET MVC and MS SQL along with Entity Framework as ORM.
I noticed that each of the stored procedures used in the EF has this common line at the start of the stored procedure definitation
IF(0=1) SET FMTONLY OFF
I thought this was a very strange condition so I googled a bit about it and also asked my co workers about it. They say that when EF maps the stored procedure it send all parameters as null and skips all the if loop. Hence it would also skip the
IF(0=1) condition and would then
SET FMTONLY OFF
On searching for
SET FMTONLY OFF MSDN, says
Returns only metadata to the client. Can be used to test the format of the response without actually running the query.
It becomes a problem when you dont control the database, you have to keep telling the DBA's to add it and explain to them over and over again why is it needed in the first place.
I still dont have a clear idea why this is required. If someone can explain this a bit in detail or guide me to some link which has this topic covered would mean the world to me.
I believe the reason is similar to the one for stored procedures that run from SSRS. In summary, when FMTONLY is active, your stored procedure may have some unexpected results. Hence the reason for explicitly turning it off. For details read Dealing with the Evil of FMTONLY from SSRS