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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy