Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mahesh_bote
Constraint Violating Yak Guru
298 Posts |
 Posted - 2007-03-01 : 08:33:47
|
| I have create one simple function which will return addition of two numbers. i tried to use SQL servers inbuild functions. e.f. i tried to use GetDate() function. it is not allowing me to use GetDate() such a way. So does it means, we can't use SQL servers inbuild functions in UDF? or something else?thanks in advance,Mahesh |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-03-01 : 08:40:14
|
| A SQL UDF must be deterministic -- with the same input, it should provide the same output, assuming the database state has not changed. Getdate() is a non-deterministic function -- the return value always varies from call to call. Thus, you cannot use Getdate() in a UDF, but you can use most other T-SQL functions. This is all in Books On Line (the SQL Server help).Also, a simple Google search for "sql server getdate() udf" will provide some work-arounds and much more information.http://www.google.com/search?q=sql+server+getdate()+udf- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-03-01 : 08:40:56
|
"we can't use SQL servers inbuild functions in UDF?"You can use in-built function inside UDF. Only thing is you can't use non-deterministic function inside UDF. Since GETDATE() is ND, you can't use it in UDF. Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
mahesh_bote
Constraint Violating Yak Guru
298 Posts |
Posted - 2007-03-01 : 08:49:03
|
quote:
Originally posted by harsh_athalye
"we can't use SQL servers inbuild functions in UDF?"You can use in-built function inside UDF. Only thing is you can't use non-deterministic function inside UDF. Since GETDATE() is ND, you can't use it in UDF.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED"
thx Harsh,can u tell me more about non-deterministic and deterministic functions? may be i have less knowladge of it.thanks in advance,Mahesh |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-03-01 : 08:51:48
|
From BOL: quote:
Functions are either deterministic or nondeterministic. They are deterministic when they always return the same result any time they are called with a specific set of input values. They are nondeterministic when they could return different result values each time they are called with the same specific set of input values.Nondeterministic functions can cause side effects. Side effects are changes to some global state of the database, such as an update to a database table, or to some external resource, such as a file or the network (for example, modify a file or send an e-mail message).
Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-03-01 : 11:18:06
|
| i know this is for sql server 2000 but note that in sql server 2005 you can put getdate inside a function.Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
|
|
|
|
|
Function within Function