user defined functions - help

  • 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 ?.

     

  • 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

  • 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

  • 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!

     

     

     

  • 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.

  • 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