June 13, 2005 at 8:52 am
CREATE FUNCTION Fn_myDate
(
@MyDate DATETIME = '01/01/1982' -- Parameter with default value
)
RETURNS @tblMyDate TABLE
([MyDate] DATETIME)
AS
BEGIN
INSERT INTO @tblMyDate VALUES ('01/01/1980')
INSERT INTO @tblMyDate VALUES ('01/01/1981')
INSERT INTO @tblMyDate VALUES ('01/01/1982')
INSERT INTO @tblMyDate VALUES ('01/01/1983')
INSERT INTO @tblMyDate VALUES ('01/01/1984')
INSERT INTO @tblMyDate VALUES ('01/01/1985')
DELETE @tblMyDate
WHERE
MyDate < @MyDate
RETURN
END
GO
-- How do we call a function with default parameter values
SELECT * FROM Fn_myDate()
/*
Server: Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function Fn_myDate.
*/
GO
SELECT * FROM Fn_myDate('01/01/1984')
/*
Works good
*/
GO
DROP FUNCTION Fn_myDate
GO
Regards,
gova
June 13, 2005 at 8:55 am
/* This how I call a stored procedure with default parameter any help with function is greatly appreciated */
CREATE PROCEDURE TEST
(@pmyDate DATETIME = NULL)
AS
SELECT ISNULL(@pmyDate, GETDATE()) myDate
GO
EXEC TEST '01/01/1980' -- Works with parameter
GO
EXEC TEST -- Works without parameter
GO
DROP PROCEDURE TEST
GO
Regards,
gova
June 13, 2005 at 8:56 am
You must pass the function an input param. If you pass a null value then the default value will be used.
HTH
Mike
June 13, 2005 at 9:00 am
Thanks Mike If that is the case with my example in post
SELECT * FROM Fn_myDate(NULL)
SELECT * FROM Fn_myDate('01/01/1982')
Both calls should give same results. But it is not. Can you please post the statement.
Regards,
gova
June 13, 2005 at 9:06 am
It's not the same behavior with functions... you must specify that you want the default parameter, like this :
Select * from dbo.Fn_myDate(default)
June 13, 2005 at 9:18 am
Thanks Remi. Why did't I try that.
I have 5 parameters in my original function and have to pass 5 defaults. I like the the way how it works with Stored procedures.
Regards,
gova
June 13, 2005 at 9:22 am
I've never found any way around that. At least the default still works and once it's coded you don't have any more work to do.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply