July 27, 2005 at 2:56 am
hi friends
Iam very new to SQL Server, Please help me how to create user defined functions using SQL Server Enterprise manager. Under which object it should be created tables, views or stored procedures ?.
July 27, 2005 at 4:17 am
These are created under "User Defined Functions". They are used in conjunction with stored procedures/ad-hoc queries and are referenced by owner.UDFname.
i.e SELECT dbo.udf_HelloWorld
Look up UDF or user defined functions here or in BOL for more information
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 27, 2005 at 11:46 am
You will find it much easier to create functions in the Query Analyzer, rather than Enterprise manager. (click tools, Query Analyzer in EM toolbar).
Here is a sample function that takes a persons ID number and returns the full name of the person.
If you want to know the name of a person, and only have his account number.
select dbo.FullName(accountnumber), accountnumber, balance
from AccountBalances
**************
This creates the function
CREATE FUNCTION FullName (@personid int)
returns varchar(50)
AS
BEGIN
declare @FullName varchar(50)
set @FullName =
(select last + isnull(', ' + nullif(first, ''), '')
from PERSON_TB ps
where pj.PERSON_ID = @personid )
RETURN @FullName
END
July 28, 2005 at 4:25 am
PMFJI but I suspect you're slightly missing the point here. As I understand it, UDF's are mostly useful to create functions that in a sense 'extend' T-SQL - adding functions that don't exist but would be nice to have; whereas the examples given are more like stored procedures.
eg (yes, I know there's better ways to do this - it's just an example!)
ALTER FUNCTION
[dbo].[hhmm] (@d datetime)
RETURNS varchar (20)
AS
BEGIN
declare @h varchar (20)
set @h = right ('00' + cast(datepart(hh,@d) as varchar),2) + ':' + right('00' + cast(datepart (n,@d) as varchar),2)
return(@h)
END
This can then be used within other queries eg
SELECT dbo.hhmm(starttime) FROM tblemployees
Hope this helps!
August 19, 2005 at 2:45 am
Actually, pq53, I presumed exactly the same thing about the use of UDFs: a nice way to extend the T-SQL function set... but I had a nasty shock.
If you try your select statement on tables with more and more rows you will see your performance going down the pan in a bad way. What you don't realise (because it's not obvious) is that your function is inner joined with tblemployees every time it needs to be evaluated, i.e. for every row in the table, meaning that your table is scanned once for every row.
BOL never describes using UDFs in this way.
August 19, 2005 at 3:14 am
Hi Gary
Yes that is a nasty shock! Do you mean it hits every row even when there's a where clause to return just one? eg
SELECT dbo.hhmm(starttime) FROM tblemployees WHERE employee_id = 12345
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply