Update field using function

Question

I have a function that gets the identifier and returns the date from the table if it exists, or returns the current date if not:

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 following queries return the correct date from the table:

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

And the following code will update the record correctly (values ​​from the table):

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

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

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

It seems that the ID parameter does not go to the function.

Answer 1

Your tests (which I missed on first reading, sorry) are enough for me to be very confused. It seems that your test results may 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 

Answer 2

Cross Apply is what you are looking for, I think.