Determine if parameter is passed or DEFAULT

Question

Determine if parameter is passed or DEFAULT

David Leaver

Mr or Mrs. 500

Points: 599
More actions
December 2, 2008 at 2:46 pm

#154976

Is there any way to determine if a stored procedure was passed a parameter if the value matches the default?
CREATE PROCEDURE uspTest (
@Param INT = 0
)
AS
blah.........
Can I tell the difference from within the procedure if I call EXEC uspTest @Param = 0 VS. EXEC uspTest?

Viewing 15 posts - 1 through 15 (of 18 total)

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

No way that I am aware of. Why would you need to know that information anyways? If you are going to do something different based upon whether or not the parameter was actually passed in - make sure the default is something that would never be passed in the first place.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

David Leaver Mr or Mrs. 500 Points: 599 More actions · Answer 2

That's how I was going to approach it, just wanted to see if there were other options. Thanks for the feedback!

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

You could always make your defaults null and then test/assign values as the first part of your proc. For example if you wanted the number 8 to be the default for the first parm, see below.

I'm still curious as to why you would want a different result.

declare @parm1Passed char(1)

set @parm1Passed = 'N'

If @parm1 = 8

Begin

set @parm1Passed = 'Y'

End

If @parm1 is null

Begin

set @parm1 = 8

End

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

I am still curious as to why you need to know if a parameter was passed in or not. It really shouldn't matter.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

Ok, Jeffrey... I'll hold him while you beat the answer out of him 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

Heh... if you need to borrow some porkchops... 😀

--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)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

Naw - have plenty of pork chops, I just need something to sling them with :w00t:

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

Note to self.... invent and patent pork chop slinger... the infomercials are going to be hilarious.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

Ninja's_RGR'us

SSC Guru

Points: 294069

December 3, 2008 at 8:39 am

#906931

Let me get in on that!

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 10

My current thinking is along the lines of a trebuchet.

Next year I'm going to have one down by my dock. Most of my buddies find no flaws in the idea of spending a day down by the lake with a cooler, a bunch of old tennis balls, and a trebuchet.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

David Leaver Mr or Mrs. 500 Points: 599 More actions · Answer 11

Yikes - Dip, Dive, Duck and Dodge! Doesn't pay to leave the forums for a few hours.

Here's the scenario - I'm taking an existing procedure that historically examined each parameter value against a known default, and if different ( and different from the existing record as well), updated a record. If the procedure had 10 params, one being the key, then it would check the other 9 params, and perform an update to the same record for each difference ( I wouldn't claim to have written this if my life was in jeopardy - even at risk of pork chops! ). I think this design was chosen to handle pre/post column level auditing.

The DAL would only pass in "changed" columns via the interface, so chances are we'd only ever get in 3 or fewer updates per record per request - still unacceptable.

I'd like to change this to a single update call, with null defaults in order to lower impact on the frontend/DAL.

So, if I were able to determine that a null value was passed into the procedure vs set as a default value, I could perform the update with a case statement. I can still do with the the default values, was just checking to see if there was an alternative.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 12

Aw rats.....

(putting down borrowed pile of pork chops)

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 13

Bummer - my new slingshot is back-ordered. Should be available and delivered in the next 6 to 8 weeks, shipping & handling charges extra (of course) :hehe:

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

Bob Hovious (12/3/2008)
My current thinking is along the lines of a trebuchet.

Huh? Don't you watch the Science Channel? Air Cannons are where its at, more than twice the range and muzzle velocity of a trebuchet.

:w00t:

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]