declare @StartDT SMALLDATETIME='2017-05-01',
@EndDT SMALLDATETIME='2017-05-30',
@BUNIT_ID INT=10084
-- '2021-05-01','2021-05-31', 10084
;WITH DateRange(DateData) AS
(
SELECT @StartDT as Date
UNION ALL
SELECT DATEADD(d,1,DateData)
FROM DateRange
WHERE DateData < @EndDT
)
SELECT [DateData],@BUNIT_ID [BunitID] INTO #DATEX
FROM DateRange
OPTION (MAXRECURSION 0);
IF 1=0
BEGIN
SET FMTONLY OFF
END
-----------------------------------------------------
Having IF(0=1) SET FMTONLY OFF seems like a risky thing to casually do in stored procedures read in by entity framework.
Entity Framework is the only source of this flag being set as a standard practice that I'm aware of (presumably other ORM's may use it).
the purpose (as I understand it) is to provide a way to get a procedures return schema without actually touching any data. (some stored procedures you don't want to execute just to update an orm's object model.
so unless you have a table that is counting the number of times your EF model has been updated (which might be interesting academically)
the safest way to use ftmonly with entity framework (in my mind) is.. under the following circumstances
at the beginning of the complex procedure do the following
if(0=1) -- if FMTONLY is on this if condition is ignored
begin
-- this loop will only be entered if fmtonly is on (ie EF schema read)
select
column1
,column2
...
,columnX
from whateverA
cross join whateverB
...
cross join whateverQ
-- joins don't matter but they might make it easier to get the column definitions
-- and names you desire. the important thing here is generating the proper
-- return schema... which is as complex as whatever you are trying to return
where 1=0
set FMTONLY off -- do this so that you can now force an early return since EF
-- usually only wants the first data set schema... other orms might
-- do something different
return -- this will be ignored if FMTONLY is still on
end