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:
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).
IF (1=0) BEGIN SET FMTONLY OFF END
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.
No comments:
Post a Comment