"Optional" values passed to a stored procedure.

[gbaughma]

I just learned this, and thought I'd share.
In a stored procedure, you can pass an optional value like this:

CREATE PROC MyProc
@Optional int = NULL
AS

BEGIN
SELECT somerows FROM sometable
WHERE
((@Optional IS NULL) OR (selctionfield = @Optional))
END

This way, if @Optional isn't passed, then all records are selected, but if @Optional IS passed, then it filters accordingly.

I thought it was spiffy, so I thought I'd share.


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

Greg,

You need to be a little careful if you plan on using this a lot. I encourage you to read this:

http://blogs.lessthandot.com/index....ming/do-you-use-column-param-or-param-is-null

Your query should work pretty well for modest sized tables, but for large tables, there will likely be a performance problem. Of course, a lot of this may depend on the actual query and if there are any mandatory fields in the search.

My point is... keep it in the back of your mind that there may be a way to improve performance if/when performance becomes a problem.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Actually.... if you are using SQL2008 or SQL2008R2 and have the latest service pack, then you query is just fine as long as you add the recompile option, like this:

CREATE PROC MyProc
@Optional int = NULL
AS

BEGIN
SELECT somerows FROM sometable
WHERE
((@Optional IS NULL) OR (selctionfield = @Optional))
[!]OPTION (RECOMPILE)[/!]
END

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

Well, I used it on a pretty small table, and the reason I did it is that I am slowly migrating some code over to pass a parameter; but I didn't want to "break" any that I might have missed; so I adapted the parameter to be optional.

<Shrug>

(I still thought it was clever...<pout>)



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[markros]

If you want to share what you learned, then I suggest to check also this

http://beyondrelational.com/justlearned/most-liked.aspx

There is JustLearned at beyondrelational.com and #JustLearned at twitter.

BTW, to confirm what George said, check also

http://social.msdn.microsoft.com/Fo...l/thread/d68dd766-446d-4cfc-bea2-4e73b7304e3f

(See Hunchback's answer).

PluralSight Learning Library

 

[gmmastros]

Greg,

My apologies. I did not mean to "Steal your thunder". What you have discovered is a very useful technique for writing code. My only intent was to provide more information.

I use optional parameters all the time, primarily so that I don't break any other code that may be floating around. When you have multiple instances of code calling the same stored procedure, you wouldn't want all (or any) of them to break simply because you added another parameter.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

George:

No offense taken. I just thought I'd share.

That's exactly the reason I did that, is to not "break" other code until I had a chance to find all of the instances where that SP may be called.

I still have SO much to learn about SQL... the funny thing is, many people see me as fluent in it; and I still consider myself a novice.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com