Professional Web Applications Themes

error in user defined function? - Microsoft SQL / MS SQL Server

Is there any way to return an error from a user defined function? Say that i have this: CREATE FUNCTION doNothing(param1 char(1) ) RETURNS nchar(10) AS BEGIN IF param1 = 'A' /* do something */ ELSE IF param1 = 'B' /*do something else */ ELSE /* Error, invalid param, do not run the function */ END I hope you get the idea. They used an invalid value for a param, and the function should not execute. Oh, and by the way... there is a nice typo for RAISERROR if you try to use it in a function. "Server: Msg 443, ...

  1. #1

    Default error in user defined function?

    Is there any way to return an error from a user defined function? Say that i
    have this:

    CREATE FUNCTION doNothing(param1 char(1) )
    RETURNS nchar(10)
    AS
    BEGIN
    IF param1 = 'A'
    /* do something */
    ELSE IF param1 = 'B'
    /*do something else */
    ELSE
    /* Error, invalid param, do not run the function */
    END

    I hope you get the idea. They used an invalid value for a param, and the
    function should not execute.


    Oh, and by the way... there is a nice typo for RAISERROR if you try to use
    it in a function.
    "Server: Msg 443, Level 16, State 2, Procedure doNothing, Line 11
    Invalid use of 'RAISEERROR' within a function."

    Didn't even know RAISEERROR existed :-)

    --
    Regards,
    Kristofer Gafvert
    http://www.ilopia.com - My personal Web Site, with information about things
    i find interesting, for example Windows Server 2003.
    Reply to newsgroup only. Remove NEWS if you must reply by email, but please
    do not.



    Kristofer Guest

  2. #2

    Default Re: error in user defined function?

    Hi

    The only method that may make sense for a scalar function would be to return
    a specific value that indicates and error

    CREATE FUNCTION doNothing(param1 char(1) )
    RETURNS nchar(10)
    AS
    BEGIN
    IF param1 = 'A'
    RETURN 'B'
    ELSE IF param1 = 'B'
    RETURN 'A'

    RETURN 'Error!!!'
    END

    SELECT Col1, dbo.doNothing(Col1)
    FROM ( SELECT 'A' AS Col1 UNION ALL SELECT 'B' UNION ALL SELECT 'C' ) A

    SELECT Col1, dbo.doNothing(Col1)
    FROM ( SELECT 'A' AS Col1 UNION ALL SELECT 'B' UNION ALL SELECT 'C' ) A
    WHERE dbo.doNothing(Col1) != 'Error!!!'


    John


    "Kristofer Gafvert" <com> wrote in message
    news:phx.gbl... 

    things 
    please 


    John Guest

  3. #3

    Default Re: error in user defined function?

    Thanks!

    --
    Regards,
    Kristofer Gafvert
    http://www.ilopia.com - My personal Web Site, with information about things
    i find interesting, for example Windows Server 2003.
    Reply to newsgroup only. Remove NEWS if you must reply by email, but please
    do not.


    "John Bell" <com> wrote in message
    news:3f488a3b$0$12646$easynet.co.uk... 
    return [/ref]
    that [/ref]
    use 
    > things 
    > please 
    >
    >[/ref]


    Kristofer Guest

Similar Threads

  1. Replies: 0
    Last Post: October 5th, 07:30 PM
  2. Replies: 2
    Last Post: October 5th, 07:20 PM
  3. Using GETDATE() within a user defined function
    By Efrain in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: August 20th, 02:03 PM
  4. Newbie - Calling a user-defined function
    By John in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 20th, 01:54 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139