I have a stored procedure which has multiple temporary tables, when I tried importing it in Entity Framework it didn't create the complex type and I get this error:
The selected stored procedure or function returns no columns
When I googled, I found adding this piece of code SET FMTONLY OFF in the stored procedure will create the complex type in my EDMX. I was able to do it.
Everything works well in Entity Framework now after adding SET FMTONLY OFF.
Now my question is, is there a security threat by adding this piece of code to my stored procedure which contains multiple temp tables ?
Thank you.
You don't want to just SET FMTONLY OFF, as that could have unintended consequences, but you could return an empty result set of the right shape, then turn FMTONLY OFF and RETURN at the beginning of your stored procedure.
eg
if 1=0 --this will only run in FMTONLY is ON
begin
select cast(1 as decimal(12,0) a, cast('x' as nvarchar(20)) b -- . . .
set fmtonly off
return;
end
Since FMTONLY ignores control flow statements it will run the code in the begin/end. The client will get an empty resultset, then the procedure exists. You have to turn off FMTONLY or else the return will not be executed, and you will get an error with your temp tables later.
When this stored procedure is run without FMTONLY the if 1=0 will be evaluated, and that block skipped.
However switching out the stored procedure for one without temp tables when you update the EF model is a valid alternative.