Bug: SQL Stored Procedure Returning -1 in Entity Framework

Occasionally I like to write about bugs I've run into recently and how I went about solving them. Not only will this hopefully help someone else fix the same bug faster, it also solidifies the solution in my mind and gives me a point of reference if I ever come across it again. So without further adieu, let's take a look at this bug!

Once connected, Visual Studio's Entity Framework typically makes the process of adding new Stored Procedures as simple as adding new tables to the model. Unfortunately, when a Stored Procedure is returning a complex type such as a set of data from a dynamic query or temporary table, Entity Framework will only return metadata about columns in its result set and execute none of the logic. This is due to the SET FMTONLY ON SQL command that Entity Framework uses before executing these special stored procedures in order to protect the data from potentially dangerous changes.

Fortunately once you are aware of this mechanism of data preservation, the fix found here from user "gotmilk13531" is actually quite simple. Altering the Stored Procedure with SET FMTONLY OFF; directly after the ALTER PROCEDURE AS or BEGIN statement as shown below:

ALTER     PROCEDURE [dbo].[se_GetSystemBOM_JF]
    @systemid int
AS
SET NOCOUNT ON
SET FMTONLY OFF;

IF EXISTS(
    SELECT * FROM tempdb.dbo.sysobjects o

or

ALTER PROCEDURE dbo.SearchProducts
  @SearchTerm VARCHAR(max)
AS
BEGIN
    SET FMTONLY OFF;
    DECLARE @query VARCHAR(max)
    SET @query = 'SELECT * FROM dbo.Products WHERE Name LIKE ''%' + @SearchTerm + '%'''
    EXEC(@query)
END

Once you've altered the Stored Procedure, load your database edmx and open the EF Model Browser. You must delete your Stored Procedure from within Complex Types, Function Imports, and Stored Procedures / Functions, then Save and I usually reload EF at this point. Next add your Stored Procedure to EF with the typical Update Model from Database method, then Save and close EF. Finally, be sure to go back and alter your Stored Procedure again to remove the SET FMTONLY OFF;.

---

I hope this quick fix helps someone else out of a similar sticky situation to mine, in a little less time than it took me to resolve it! Thanks for reading and I'll see you all next week.