CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduressql-function
ceth
ceth

Reputation: 45295

Update field by a function

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.

Upvotes: 4

Views: 186

Answers (2)

MatBailie
MatBailie

Reputation: 86706

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

Upvotes: 2

Jahan Zinedine
Jahan Zinedine

Reputation: 14874

Cross Apply is what you looking for I think.

Upvotes: 0

Related Questions