Sql server evaluate all then part of case when-CodePudding

I create this fuction:

CREATE function ml.fn_Temp() returns @ResultTable table(Code int) as
    begin
        declare @Id int = (select 1 / 0)
        return 
    end

I run this quey :

declare @AccFieldKindName int = 9
select
    case 
        when @AccFieldKindName = 7 then 
                (select Code from ml.fn_Temp())
        when @AccFieldKindName = 9 then 67777
    end

at result it encounter with error : Divide by zero error encountered. I think this is not correct to run code that its's condition is not true. why sql run all query. in reality ml.fn_Temp is aheavy query. In each run sql run this function so cause performance problems. Can I change this behaviour, to run only part of case when that it's condition is true. I don't want to change this query to If ... else .

CodePudding user response：

This is very much expected.

A non correlated multi statement TVF always executes before the query that references it and populates the table variable of results.

There is a sequence operator that runs that first and the rest of the plan afterwards. So the error is encountered before execution of the CASE even begins

Your demo code is not realistic but maybe you could use an inline table valued function instead in your actual scenario.

Or use a local table variable/temp table prior to executing the query to conditionally call the function when needed

DECLARE @AccFieldKindName INT = 9
DECLARE @fn_Temp TABLE
  (
     Code INT
  );

IF @AccFieldKindName = 7
  BEGIN
      INSERT @fn_Temp
             (Code)
      SELECT Code
      FROM   dbo.fn_Temp()
  END

SELECT CASE
         WHEN @AccFieldKindName = 7 THEN (SELECT MIN(Code) FROM   @fn_Temp)
         WHEN @AccFieldKindName = 9 THEN 67777
       END