When working with a LINQ to SQL model (dbml) and you try to add a custom stored procedure that uses dynamic SQL, you will likely see an error similar to the following:
The return type for the following stored procedure could not be detected. Set the return type for each stored procedure in the Properties window.
Visual Studio will detect dynamic SQL as the return type of a stored procedure when you return columns from temporary tables, for example.
How to resolve? Relatively easy actually. At the start of your stored procedure, add the following line:
SET FMTONLY OFF
Update the procedure to include the above command and back in Visual Studio, refresh your database connection before trying to add the procedure to the model. This time, Visual Studio is able to work out the return columns and their type. Once you’ve added it to the model, remove the command from the stored procedure in SQL.
What does setting FMTONLY to off do? Well, while this set to off, running the procedure only returns the metadata, no rows are returned. By doing this Visual Studio is able to determine the columns. Simple! 🙂
