Post reply

Default Parameter to a store procedure

  • September 9, 2010 at 10:37 am

    #227633

    I have a procedure with has 3 parameter

    param1 INT,

    param2 INT = NULL,

    param3 INT = NULL

    How do I know at run time parameter that Param2 and param3 used default vaule NULL or passedin value NULL

    Thanks

  • September 9, 2010 at 10:40 am

    #1218779

    This procedure only requires param1 have a value. If you call the proc and pass @param1 = 5, then the other 2 will use the default of null. If you call the proc and pass @param1 = 5 and @param2 = 7 then @param2 will use 7 and @param3 will use null. The default is only used if the param is not passed in at all. If @param1 is not passed in then you will receive an error

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • September 9, 2010 at 10:48 am

    #1218786

    Yes I knoe it will use Null if value not passed in

    but is their a way to chack at run time to see it's uses default value or passed in value

  • September 9, 2010 at 10:53 am

    #1218794

    Yes, you need to define an impossible value for the parameter and set that as the default value

    IE : @Age = -987654321

    Then you can check between not supplied and NULL "value".

  • September 9, 2010 at 10:54 am

    #1218797

    Why do you care?

    NULL is null, whether the user passes it or not.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • September 9, 2010 at 10:55 am

    #1218799

    Lets say I have default value as 1 then what??

  • September 9, 2010 at 10:58 am

    #1218801

    jagat.patel (9/9/2010)

    Lets say I have default value as 1 then what??

    Assuming you can answer the "why do you care" question...

    you can run if @Age = -987654321... do whatever.

    I've never seen that very usefull unless I was building dynamic sql.

  • September 9, 2010 at 11:01 am

    #1218805

    yes for that I want to check what going on may be you idea is batter to sat som impossible value as default then NULL

    but their is no way of any flag to know how parameter got that vaule

    Thanks

  • September 9, 2010 at 11:08 am

    #1218809

    Well ya, add another parameter for each "nullable" parameter that says wether the user set it to null or wether you did it yourself in the application. That's longer to code but it works better than an impossible value (because sometimes you don't have one).

    However I'd like to point out again than in 90% of the cases it makes no difference, hence the comment you got from Steve earlier.

Viewing 9 posts - 1 through 8 (of 8 total)

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