Default Parameter to a store procedure

Question

Default Parameter to a store procedure

jagat.patel

SSC Eights!

Points: 908
More actions
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

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

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

Mike01 SSChampion Points: 11395 More actions · Answer 1

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/

jagat.patel SSC Eights! Points: 908 More actions · Answer 2

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

Why do you care?

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

jagat.patel SSC Eights! Points: 908 More actions · Answer 5

jagat.patel

SSC Eights!

Points: 908

September 9, 2010 at 10:55 am

#1218799

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

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.

jagat.patel SSC Eights! Points: 908 More actions · Answer 7

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

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.