User Defined Function
-
I'm writing a function which would return a particular employee total experience from the master table. The function is as follows:
create function dbo.get_xperience
(@empno varchar(6))
returns int
as
begin
declare @experience int
select @experience = round(datediff(mm,Joining_Date,isnull(Resignation_Date,getdate())) + isnull(Employee_Prev_Exp,0),0)
from employee_mstr_tble
where emp_no = @empno
return @experience
end
I'm getting "Invalid use of 'getdate' within a function"
But when the select statement is taken alone and executed, it works fine. What is the place where i'm going wrong.?
-
GETDATE() is not allowed insede UDF.
And it's good. It force you to make your code better.
This function is more universal: it will return exprerience on any given date.
-------------------------------
create function dbo.get_xperience
(@empno varchar(6),
@OnDate datetime)
returns int
as
begin
declare @experience int
select @experience = round(datediff(mm,Joining_Date,isnull(Resignation_Date, @OnDate)) + isnull(Employee_Prev_Exp,0),0)
from employee_mstr_tble
where emp_no = @empno
return @experience
end
GO
DECLARE @Date datetime
SET @Date = GETDATE()
SELECT dbo.get_xperience ('123456', @Date)
_____________
Code for TallyGenerator -
I agree with Serqiy on this one... your code is better if you don't use a UDF here.
In SQL 2005, you can use GETDATE() in a UDF... you can also trick SQL 2000 into doing it, as well, should a valid need ever arise (I actually haven't found a valid need, yet)...
Create a view like this...
CREATE VIEW Now AS SELECT GETDATE() AS Now
...and then you can reference NOW from a UDF as you can any view.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply