For exemplification the following piece of code would run successfully on SQL Server 2005 but fail on SQL Server 2000, returning the below error message.
-- creating the test function CREATE FUNCTION dbo.fGetCurrentDate() RETURNS smalldatetime AS BEGIN RETURN GetDate() END -- testing the function SELECT dbo.fGetCurrentDate()
Error message:
Server: Msg 443, Level 16, State 1, Procedure GetCurrentDate, Line 6 Invalid use of 'getdate' within a function.
Server: Msg 443, Level 16, State 1, Procedure GetCurrentDate, Line 6 Invalid use of 'getdate' within a function.
A similar error message resulted when using the GetDate() function inside of a view:
-- creating the test view CREATE VIEW dbo.vGetCurrentDate AS SELECT GetDate() AS CurrentDate
-- testing the view SELECT * FROM dbo.vGetCurrentDate
The alternative was to use an UDF instead, which functions like a parameterized view, something like:
-- creating the test UDF CREATE FUNCTION dbo.udfFilterByDate( @date datetime) RETURNS TABLE AS RETURN( SELECT * FROM [AdventureWorks2014].Production.Product WHERE [SellStartDate]>=@Date) -- testing the UDF SELECT * FROM dbo.udfFilterByDate(GetDate()) -- testing the UDF (alternative) DECLARE @date as datetime SET @date = GetDate() SELECT * FROM dbo.udfFilterByDate(@date)
Using an UDF can still be necessary when is needed to pass dates and other types of parameters used inside of a correlated query or within the logic.
No comments:
Post a Comment