CODEWITHSUNDEEP
sqlsql-server
Paul Michaels
Paul Michaels

Reputation: 16685

Update SQL Server table with interdependant functions

Have the following update statement in SQL Server:

UPDATE MY_TABLE
SET COLUMN1 = FN_GETFIRSTVALUE(T.VALUE1),
    COLUMN2 = FN_GETSECONDVALUE(T.VALUE1, T.COLUMN1)
FROM MY_TABLE AS T
INNER JOIN ...

So FN_GETSECONDVALUE accepts an input parameter which is returned from FN_GETFIRSTVALUE. I thought about using a table function... but I can't see how I could update two columns with this. Is there another / better way to do this? I don't want to call the function twice, or split it into a select / update.

Upvotes: 1

Views: 1226

Answers (1)

MatBailie
MatBailie

Reputation: 86706

The smallest change to your example would be as follows...

UPDATE
  MY_TABLE
SET
  COLUMN1 = FN_GETFIRSTVALUE(T.VALUE1),
  COLUMN2 = FN_GETSECONDVALUE(T.VALUE1, FN_GETFIRSTVALUE(T.VALUE1))
FROM
  MY_TABLE AS T
INNER JOIN
  ...

SQL server is good at re-using results and so the first function does not necessarily get executed twice. It is, however, still a bit messy to even write the call to the function twice.

Instead you can write the re-use yourself using APPLY...

UPDATE
  MY_TABLE
SET
  COLUMN1 = first_result.val,
  COLUMN2 = second_result.val
FROM
  MY_TABLE AS T
OUTER APPLY
  (SELECT dbo.FN_GETFIRSTVALUE(T.VALUE1) AS val)          AS first_result
OUTER APPLY
  (SELECT dbo.FN_GETSEOCNDVALUE(first_result.val) AS val) AS second_result
INNER JOIN
  ...

Even better, you can re-write your function as a Table Valued Function. Even if it only returns one field in one record...

UPDATE
  MY_TABLE
SET
  COLUMN1 = first_result.val,
  COLUMN2 = second_result.val
FROM
  MY_TABLE AS T
OUTER APPLY
  dbo.FN_GETFIRSTVALUE(T.VALUE1)           AS first_result
OUTER APPLY
  dbo.FN_GETSEOCNDVALUE(first_result.val)  AS second_result
INNER JOIN
  ...

In both cases this performs much better if the Table Valued functions as defined as Inline-Functions (just a single query) than if the are defined as Multi-Statement-Functions (including internal variables, IF statements, etc).

Finally, depending on your needs you can even wrap both functions into one...

UPDATE
  MY_TABLE
SET
  COLUMN1 = result.val1
  COLUMN2 = result.val2
FROM
  MY_TABLE AS T
OUTER APPLY
  dbo.FN_GETBOTHVALUES(T.VALUE1)           AS result
INNER JOIN
  ...

(Where the function returns one record with 2 fields, named val1 and val2.)

So, all in all, you have a whole host of options here. And that's just using APPLY, there are other option with Common Table Expressions...

WITH
  step1 AS
(
  SELECT
    *,
    dbo.FUNCTION1(T.column1) AS result1
  FROM
    MY_TABLE AS T
  INNER JOIN
    ...
)
,
  step2 AS
(
  SELECT
    *,
    dbo.FUNCTION2(T.result1) AS result2
  FROM
    step1
)

UPDATE
  step2
SET
  column1 = result1,
  column2 = result2

So many options...

Upvotes: 3

Related Questions