Update field by a function

Question

I have the function which gets ID and returns date from table if it exists or returns current date if isn't:

CREATE FUNCTION [dbo].[CLOSEDATE] (@ID int)
RETURNS datetime
AS
  BEGIN
     DECLARE @closed int;
     DECLARE @result datetime;

     SELECT @result = created_on from dbo.statuses_history
            WHERE journalized_id = @ID and new_status = 'Закрыто';

     IF @result IS NULL    
        SELECT @result = GETDATE()     

     RETURN (DATEADD(dd, 0, DATEDIFF(dd, 0, @result)))
  END;

The next queries return correct date from table:

select dbo.closedate(4170)
select dbo.closedate(id) from issues where id = 4170

And the next code update the record correctly (values from table):

DECLARE @d AS datetime
select @d = dbo.closedate(4170)
UPDATE issues SET created_on = @d  WHERE issues.id = 4170

But I get current date in the field if I update the record:

UPDATE issues
SET created_on = dbo.CloseDate(id)
WHERE issues.id = 4170

It looks like the ID parameter doesn't pass to the function.

yes, i have tried. it doesn't matter – ceth Dec 15 '11 at 08:36 — ceth, Dec 15 '11 at 08:36

score 2 · Accepted Answer · answered Dec 15 '11 at 09:08

Your tests (that I missed on the first reading, sorry) are enough to make me very confused. It seems that your test results should not be possible.

My only suggestion would be to recode the function and see what happens...

CREATE FUNCTION [dbo].[CLOSEDATE] (@ID int)
RETURNS TABLE
AS
RETURN
  SELECT
    (DATEADD(dd, 0, DATEDIFF(dd, 0, ISNULL(MAX(created_on), GetDate())))) AS close_date
  FROM
    dbo.statuses_history
  WHERE
    journalized_id = @ID
    AND new_status = 'Закрыто'

And then...

UPDATE
  issues
SET
  created_on = fn.close_date
FROM
  issues
CROSS APPLY
  dbo.CLOSEDATE(id) AS fn
WHERE
  issues.id = 4170

Thanks, but I find better solution - remove the function and create one UPDATE-query. — ceth, Dec 15 '11 at 09:13

score 0 · Answer 2 · answered Dec 15 '11 at 08:36

0

Cross Apply is what you looking for I think.

answered Dec 15 '11 at 08:36

Jahan Zinedine

14,616
5
46
70

Sql does this operations set based not by row – Jahan Zinedine Dec 15 '11 at 08:37
As I see CROSS APLLY works with functions which returns table, but my function returns scalar value – ceth Dec 15 '11 at 08:49
@Jani - Only if the function is reformed as a Table Valued Function (this is a scalar function at present). And the benefits are negligible unless it is also an Inline function rather than a Multi Statement function. Would you care to code up an example for the OP, where CROSS APPLY will work *and* yield benefits? (I can post a separate answer if not.) – MatBailie Dec 15 '11 at 08:49

Update field by a function

2 Answers2