SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Intro to User Defined Functions (Updated)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 01/07/2001 :  18:42:41  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Garth Wells sends us this article on User Defined Functions. It's taken mainly from his book, Code-Centric: T-SQL Programming with Stored Procedures and Triggers, about programming Microsoft SQL Server. He also has a web site for the book with sample chapter downloads. Thanks for the article Garth!

Article Link.

Anonymous
Starting Member

0 Posts

Posted - 01/15/2001 :  15:11:42  Show Profile  Reply with Quote
the end of month function doesn't work right for dates in March, May, July and others

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 01/15/2001 :  18:16:06  Show Profile  Reply with Quote
LDOM Function Code Updated

The code has been modified to address the bug detailed in a previous comment.

Go to Top of Page

JoeyBogus
Starting Member

2 Posts

Posted - 10/12/2001 :  16:38:46  Show Profile  Reply with Quote
Just an observation but it looks like a function must be invoked using the dbo. identifier even if the user executing it is the dbo. Just curious whether this is by design or if I'm not doing it right?

For example:

Assuming I am logged in using the sa account, calling the function like this won't work for me:

SELECT fx_LDOM (GETDATE())

However, using the dbo. identifier makes it work fine:

SELECT dbo.fx_LDOM (GETDATE())

Any ideas or am I just crazy???
Go to Top of Page

PiecesOfEight
Posting Yak Master

USA
200 Posts

Posted - 10/12/2001 :  17:43:13  Show Profile  Reply with Quote
This is from info in Professional SQL Server 2000 Programming. Create the fucntion in master with an fn_ prefix and then change its ownership. Doing this makes SQL treat it like a system function, so you can call it from any db w/o a prefix. However, you won't be able to drop it later unless you run sp_configure. So here's the whole thing -- be careful with sp_configure and "allow updates" b/c you can trash system tables and SQL won't stop you.

use master
go
create function fn_testfunc (@int int)
returns int
as
begin
return(@int)
end
go

exec sp_changeobjectowner 'fn_testfunc','system_function_schema'

use pubs
go
select fn_testfunc(9)

use master
go

exec sp_configure 'allow updates',1

reconfigure with override
go

drop function system_function_schema.fn_testfunc

exec sp_configure 'allow updates',0

reconfigure with override
go


Go to Top of Page

JoeyBogus
Starting Member

2 Posts

Posted - 10/18/2001 :  10:26:13  Show Profile  Reply with Quote
Hmmm... this is intriguing but I guess I was expecting functions to work in a similar fashion as stored procedures... having the object in master makes my stomach hurt!

Go to Top of Page

filisosa
Starting Member

Mexico
1 Posts

Posted - 02/13/2008 :  15:54:23  Show Profile  Visit filisosa's Homepage  Click to see filisosa's MSN Messenger address  Reply with Quote
I am trying to do this in SQL Server 2005 and it does not work the same. First it tells me that the object system_function_schema does not exist. Ant then I use the sys schema, but it tells me that I am not the owner of the object "sys".


CREATE FUNCTION fn_dtt ( @as_fecha_ddmmyyyy varchar(12) )
RETURNS Datetime
with schemabinding AS
BEGIN
return convert( datetime, @as_fecha_ddmmyyyy, 103 )
END

go

exec sys.sp_changeobjectowner 'fn_dtt', 'sys'


This is not working, any ideas ?
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 02/14/2008 :  06:01:31  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
filisosa,

This forum is not for posting questions, just for comments and discussions on article posted. Please create separate thread for your question.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000