Post reply

sql function - null parameter

  • August 17, 2009 at 9:53 am

    #138150

    alter fnEmp

    (@empid)

    begin

    select * from tblemp

    or

    select * from tblemp where empid=@empid

    in the above function how can i handle parameter when there is no value or null value.

    I dont want the function to fail when a paramter is passed or not, in both scenarios it should exec either of the query.

  • August 17, 2009 at 10:02 am

    #1040311

    select * from tblemp where empid=isnull(@empid,empid)

    You can change your select to something like above. Does that work?

  • August 17, 2009 at 10:06 am

    #1040319

    in that case i think i have to change parameter to

    alter fnEmp

    (

    @empid vatchar(5)=null

    )

    right?

  • August 17, 2009 at 10:21 am

    #1040340

    I don't think default values work for functions, though I could be wrong. Either way though, what Matt wrote will work, you don't really need to change your function declaration at all.

  • August 17, 2009 at 11:44 am

    #1040380

    I believe that is correct... you actually have to use the word "DEFAULT" for unknown parameters in a function.

    You should also be aware that functions cannot return result sets directly. They can only return result sets as a Table Valued Function and then you must use the function in the FROM clause of the calling proc or script.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply