Function default parameter

I am creating a udh function that requires to know the current date.
Since GetDate() can't be used inside a UDF I thought
I would use a date parameter with a default value of GetDate()
i.e.:
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate()
) Returns
or
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = (Select GetDate())
) Returns
These do not even compile
However
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate
) Returns
Does but when I call it it
Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
It tells me that string cannot be converted to a date
Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
Syntax error converting datetime from character string.
Any Idea...
Thanks
Fred
Hi Fred,
You can't use a function as the default value for a parameter, only
literals. You can use a view that just returns the value of GETDATE() for
example:
CREATE VIEW vw_getdate
AS
SELECT GETDATE() AS getdate
but the view will be accessed each time the function is executed, which
means that you can end up with having different values for each execution.
The safest is not to use a default value and just pass in the value for
GETDATE().
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:7315A12D-37ED-4F5F-8855-2DFE1F2153E6@.microsoft.com...
>I am creating a udh function that requires to know the current date.
> Since GetDate() can't be used inside a UDF I thought
> I would use a date parameter with a default value of GetDate()
> i.e.:
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate()
> ) Returns
> or
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = (Select GetDate())
> ) Returns
> These do not even compile
> However
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate
> ) Returns
> Does but when I call it it
> Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
> It tells me that string cannot be converted to a date
> Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
> Syntax error converting datetime from character string.
> Any Idea...
>
> Thanks
>
>
> --
> Fred

Function default parameter

I am creating a udh function that requires to know the current date.
Since GetDate() can't be used inside a UDF I thought
I would use a date parameter with a default value of GetDate()
i.e.:
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate()
) Returns
or
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = (Select GetDate())
) Returns
These do not even compile
However
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate
) Returns
Does but when I call it it
Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
It tells me that string cannot be converted to a date
Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
Syntax error converting datetime from character string.
Any Idea...
Thanks
--
FredHi Fred,
You can't use a function as the default value for a parameter, only
literals. You can use a view that just returns the value of GETDATE() for
example:
CREATE VIEW vw_getdate
AS
SELECT GETDATE() AS getdate
but the view will be accessed each time the function is executed, which
means that you can end up with having different values for each execution.
The safest is not to use a default value and just pass in the value for
GETDATE().
--
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:7315A12D-37ED-4F5F-8855-2DFE1F2153E6@.microsoft.com...
>I am creating a udh function that requires to know the current date.
> Since GetDate() can't be used inside a UDF I thought
> I would use a date parameter with a default value of GetDate()
> i.e.:
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate()
> ) Returns
> or
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = (Select GetDate())
> ) Returns
> These do not even compile
> However
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate
> ) Returns
> Does but when I call it it
> Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
> It tells me that string cannot be converted to a date
> Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
> Syntax error converting datetime from character string.
> Any Idea...
>
> Thanks
>
>
> --
> Fred

Function

Can a UDF return a table query as result? Please kindly provide sample T-SQL. Thanks.Yes. Check out books on line under UDF.|||In Northwind database I try to create and test the sample UDF shown in BOL as below:
CREATE FUNCTION LargeOrderShippers ( @.FreightParm money )
RETURNS @.OrderShipperTab TABLE
(
ShipperID int,
ShipperName nvarchar(80),
OrderID int,
ShippedDate datetime,
Freight money
)
AS
BEGIN
INSERT @.OrderShipperTab
SELECT S.ShipperID, S.CompanyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S INNER JOIN Orders AS O
ON S.ShipperID = O.ShipVia
WHERE O.Freight > @.FreightParm
RETURN

SQ analyser displays the following:
Server: Msg 170, Level 15, State 1, Procedure LargeOrderShippers, Line 18
Line 18: Incorrect syntax near 'RETURN'.

Please advise.
Thanks much.
|||Try this link for all the info you need about UDF (user defined functions), the person who runs the site is a UDF expert. Hope this helps.
http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-38-udf_DT_AddTime.htm|||check thislink|||

The syntax for it is :
create function <function name ( param 1 <datatype>,...)>
returns table
as
return
select .....
go

Hope this solves your query...
Cheers
Ajay G

|||Sorry, I missed the "END" at the end. After I add it back, the UDF is created and runs OK.
Thanks much.