I've a Function that I have set to return an INT week number that is calculated from a date passed to the function, Occasionally the function returns NULL, which is what I expect now and again, but instead of wrapping the calling function in ISNULL to return "N/A" how can I do this within function.
Can more than one data type be returned - not more than one at a time - but either INT or Varchar if the returning value is NULL? My scalar function is as below. It creates a temp table of week number (1-4) for a 28 day period from the Last Saturday then use this to return the associated week number of the date passed in to the function:
ALTER FUNCTION [dbo].[daterangeweeknumber]
(
-- Add the parameters for the function here
@refdate datetime
)
RETURNS int
AS
BEGIN
-- Run procedure to populate table
-- Declare the return variable here
DECLARE @weeknumber int
DECLARE @mytemptablevariable table (weeknumber INT,fromdate DATETIME,todate DATETIME)
DECLARE @lastsaturdaydate DATETIME
set @lastsaturdaydate = DATEADD(DAY, DATEDIFF(DAY, '18991230', GETDATE()) / 7 * 7, '18991230')
-- Add the T-SQL statements to compute the return value here
INSERT INTO @mytemptablevariable
values (1, DATEADD(DAY, -27, @lastsaturdaydate), DATEADD(DAY, -21, @lastsaturdaydate))
INSERT INTO @mytemptablevariable
values (2, DATEADD(DAY, -20, @lastsaturdaydate), DATEADD(DAY, -14, @lastsaturdaydate))
INSERT INTO @mytemptablevariable
values (3, DATEADD(DAY, -13, @lastsaturdaydate), DATEADD(DAY, -07, @lastsaturdaydate))
INSERT INTO @mytemptablevariable
values (4, DATEADD(DAY, -6, @lastsaturdaydate), @lastsaturdaydate)
select @weeknumber = weeknumber
from @mytemptablevariable
where @refdate >= fromdate AND @refdate <= todate
-- Return the result of the function
RETURN @weeknumber
END
EDIT: And I just realised can't return INT and Varchar to same column (DOH!) and changing return just to varchar would make everything much simpler!
Use the following wherever you call the function
SELECT ISNULL(CAST(dbo.daterangeweeknumber(GETDATE()) AS VARCHAR(10), 'N/A')