Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    1

    Unanswered: User Defined Functions

    When I define a UDF in SQL 2000 I can't execute it without qualifiction. Here's the code:
    CREATE FUNCTION GetLastShipDate(@DO_No CHAR(10))
    RETURNS CHAR(10)
    -- DROP FUNCTION GetLastShipDate
    BEGIN
    DECLARE @v_last_ship_date CHAR(10)

    SELECT @v_last_ship_date = COALESCE(CONVERT(CHAR(10), MAX(Asn.Asn_Ship_DateTime), 110), '')
    FROM Asn, Asn_Do
    WHERE Asn_Do.Asn_Number = Asn.Asn_Number
    AND Asn_Do.DO_Number = @DO_No
    AND Asn.ASN_STATUS in ('SENT','RESENT')


    RETURN @v_last_ship_date
    END

    So I have to execute this with:

    SELECT dbo.GetLastShipDate('T010215004')

    I want to execute it with

    SELECT GetLastShipDate('T010215004')

    What am I doing wrong?

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: User Defined Functions

    welcome in the wonderfull world of SQL Server

    where you HAVE to prefixe all functions and stored proc.


    < dbo. > will become you're best friend



    (had the same question a few weeks before... had fun changing ALLLL my functions to dbo.gnagnagna)

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You don't have to reference the owner of stored procedures. And oddly, you don't need to reference the owner of table functions either. Just scalar functions.

    Why? Dunno.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    what do you mean by scalar UDF ???

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Scalar returns a single value. Getdate(), for instance, is a scalar function. Table functions return data sets.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    Ah !


    but all UDFs are to be prefixed with the owner

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Only scalar, but it's a good habit.

  8. #8
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    noted !


    thanks all

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •