Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Default Values and Named Parameters for Stored Procs


Default Values and Named Parameters for Stored Procs

Author
Message
Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7231 Visits: 2679
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/defaultvaluesandnamedparametersforstoredprocs.asp>http://www.sqlservercentral.com/columnists/awarren/defaultvaluesandnamedparametersforstoredprocs.asp

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
well0549
well0549
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
Points: 281 Visits: 2
I would say it is always nice to have a default behaviour for a stored proc.
If you dont give it any parameters it will perform the default behaviour.

But when you need a different behaviour you could call the sproc with a parameter.

I don't see anything wrong with this......

If anybody could tell me why this is wrong I could learn from them.



Andy Jones, DBA
Andy Jones, DBA
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1178 Visits: 535
I like default parameters to specify default behaviour. If a default value needs to change (customer requirement etc..) this change only has to be made in one place, whereas if you hadn't used a default this change would have to be made in every calling piece of code, maybe in multiple applications.

Regards,
Andy Jones

.
Antares686
Antares686
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: Moderators
Points: 8426 Visits: 780
I like them primarily when I may have a value that can be NULL just so that all the parameters do not need to be expressed. But to make usefull to most folks you need to put defaulted values toward the end otherwise they in some cases end up doing more work than needed.

Ex.

CREATE PROC ip_test

@val1 as varchar(4) = NULL,
@val2 as varchar(4) = NULL

nw I want to set @val2 to TEST and leave @val1 out

ip_test @val2 = 'TEST'
ip_test NULL, 'TEST'

So there you see the value list was shorter to code than having to specify @val2. This sometimes can be a drawback if you use highly desciptive variable names one small data values. It is just a nicity that you have the option.

However in the example if I wanted to set @val1 to TEST then I would do

ip_test 'TEST'

which is short than the other two. My opinion is supply all values up to the last item you need and anything that can be defaulted put at the end of you list of inputs. Thus making it of the most possible use.



Pat6295
Pat6295
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 1
I find default parameters very useful as I work in an environment where I often have an Access application sharing an SPROC with a web page where a parameter might be needed for the web page but is always the same coming from the application.

Patrick Edgerton
DBA Resource Concepts



Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7231 Visits: 2679
Thanks for your comments so far!

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
mromm
mromm
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 1
Just one little drawback of using default values for SP parameters...

Think of default values as you think of late binding in programming languages. If you forgot to specify a parameter for your SP, it won't give you a compile-time error. Instead it will run as if everything is fine. Even if you put an appropriate parameter validation in the beginning of SP, you will see it only at execution time, not at compile time.



dj_meier
dj_meier
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 Visits: 29
I find default parameters to be very useful when a single stored proc is being used by multiple versions of client software (ie-web, client/server). If one of the applications supports additional functionality and parameters, then it can be implmented with no code changes to the legacy applications.

-Dan


-Dan
Clarence R. Assey
Clarence R. Assey
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 16

Tinkering with stored procedure parameters

Theoretically what i did is to make the procedures behave as if they do not have parameters when no parameter is passed to the procedure when it is called. This equivalent of passing a null to the procedure parameter...

Therefore if you call the procedure with no parameters the procedure will return all values...you will then only need to specify values for the parameters you need.

To achieve this instead of specifying your parameter as say:-

...WHERE CustomerID=@CustomerID specifiy

....WHERE CustomerID=COALESCE(@CustomerID,CustomerID)

With fine tuning you will find great potential of using stored procedure...

Contact me at clarence_assey@hotmail.com for more help


Tharaka-713569
Tharaka-713569
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 2
Actually last week i coded my application with a parameter refresh. but i think this is more handy... ok now im going to go back and check whether my asp application can pass the parameters as u have mentioned. if its works... Thank you Maan...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search