January 23, 2017 at 8:01 am
i have a table called Config with columns id and value.
id holds data called 1 and value holds data called hardcoded date '11/6/2010'
i have a function where it returns datetime
(@Sa varchar(30),
@type varchar(30))
returns datetime
as
BEGIN
declare @ret datetime
SELECT @ret = CASE WHEN MAX(Date) IS NULL
THEN '11/6/2010'
ELSE MAX(Date)
END
from dbo.EmpDate where Sa= @Sa and type = @type
return @ret
END
Now instead of directly hardcoding the date in the function, I want to join with config table to get the hardcoded date.
January 23, 2017 at 8:15 am
SELECT @ret = CASE WHEN MAX(Date) IS NULL
THEN c.Value
ELSE MAX(Date)
END
from dbo.EmpDate e
JOIN Config c ON c.id = 1
where e.Sa= @Sa and e.type = @type
return @retBe careful with scalar-valued functions, though: they're performance killers. If you use them in a query, the value is calculated individually for each row. Consider converting to an inline table-valued function instead.
John
January 23, 2017 at 8:23 am
John Mitchell-245523 - Monday, January 23, 2017 8:15 AMSELECT @ret = CASE WHEN MAX(Date) IS NULL
THEN c.Value
ELSE MAX(Date)
END
from dbo.EmpDate e
JOIN Config c ON c.id = 1
where e.Sa= @Sa and e.type = @type
return @retBe careful with scalar-valued functions, though: they're performance killers. If you use them in a query, the value is calculated individually for each row. Consider converting to an inline table-valued function instead.
John
How to convert this as an Table Valued Fucntion?
January 23, 2017 at 9:09 am
Also, to reduce errors and potential errors, don't code dates in an ambiguous format, i.e.,
does '11/6/2010' represent Nov 6 or Jun 11??
or waste space with formatting chars.
Instead, use YYYYMMDD format, like so:
'20101106'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply