Need help with this

Question

Need help with this

Smartdog

Old Hand

Points: 326
More actions
January 28, 2009 at 7:18 am

#199258

I have a stored procedure, and um passing a parameter in it as a number,
all I want is to check, if the parameter is empty I'll not check anything upon it
if the parameter contains number I'll Get all rows having number less than it ..
In other words ..
@IDparameter
select ID from someTable
where if(not(@IDparameter='')) ID<@IDparameter
in usual i can operate it directly like :
@IDparameter
select ID from someTable
where ID<@IDparameter
but i want to check if the parameter is empty string so i'll not do any operation on it (return all rows)
thx in advance

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

I would strongly suggest, if this is for a procedure, call sub-procedures instead of hving all the possible selects in the one procedure. There's a big parameter sniffing problem if you do that.

So, it would be something like this:

CREATE PROCEDURE DoSearch (@SomeParam int = NULL)

AS

IF @SomeParam IS NULL

EXEC ProcToReturnAllValues

ELSE

EXEC ProcToReturnLessThat @SomeParam

Then for the two subprocedures you don't need to worry if the param was passed or not. In the first, there's no parameter, in the second it will have a value (the IF takes care of that)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Smartdog Old Hand Points: 326 More actions · Answer 2

although ur solution may be working in this case, but it will not be working when passing 7 parameters as i want to do, i thought about dynamic sql and it worked previously with me, but i thought about a method like 'if else' or 'case when then', and i don't want to make tens of procedures for just this case, wish anybody can help me with more dynamic solution ..

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Smartdog (1/28/2009)
although ur solution may be working in this case, but it will not be working when passing 7 parameters as i want to do,

If you'd said in your first post that you had 7 parameters, I'd have given you a solution that worked for 7. You mentioned one.

Dynamic SQL is the way to go with multiple optional parameters. There are a number of tricks people use with where clauses that have multiple predicates of the form (col1 = @var1 OR @var1 IS NULL), however they do not perform well.

Be sure you're familiar with all the downsides of dynamic SQL, and read through this article - http://www.sommarskog.se/dyn-search.html

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Smartdog Old Hand Points: 326 More actions · Answer 4

Big thanks to you, I'll try the first one, if it didn't work I'll go Dynmaic SQL ...