sql query to avoid null value parameter

Question

these are the variables i am passing to some method where i need to write some sql query like

string cmd = @"select *
from students_tbl
where
     course_id=@courseId and branch_id=in(+" branchId "+)
and passout_year>=@passoutYear
and current_backlog>=@currentBacklog
and gender=@gender
and eGap<=@eGap
and first_year_percent>=@firstyearPercent
and second_year_percent>=@seconYearPercent
and third_year_percent>=@thirdyearPercent";

and so on but problem is that few of these parameters are optional means for those variable there value is null so i don't want to include those parameter in query so how should i eliminate those null variable i am not getting how should i write query to solve this issue

those parameter are random nothing fix when they will be null because hey are optional so how should i write query by using only not null parameter

those paameter are random nothing fix when they will be null — NoviceToDotNet
– NoviceToDotNet, Commented Aug 16, 2010 at 13:28

D'Arcy Rittich · Accepted Answer · 2010-08-16 13:28:46Z

1

Just add an is null check before your comparison, which will short-circuit if the input parameter value is null, e.g.:

where (@currentBacklog is null or current_backlog >= @currentBacklog)

answered Aug 16, 2010 at 13:28

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

NoviceToDotNet Over a year ago

if i put IS NULL like you did then that parameter would not be taken in account? that's what i want please let me know

NoviceToDotNet Over a year ago

i am afraid that you didn't understand me it may sound rude but from the above code what i figure out htat it would look for those value in the data base which are null for current backlog or greater than the @currentBacklog But is it true that by the above code it would not take current_backlog>=@currentbakclog in account i mean it will completey ignore it and filter it out then i wil run the query ?

NoviceToDotNet Over a year ago

i am not making stored procedure it's simple parametrized query

D'Arcy Rittich Over a year ago

@Novice: That is incorrect, the above sample does not match records where current_backlog is null, rather, if the @currentBacklog parameter is null (i.e., not supplied, or supplied as null), then the condition will evaluate to true and the second clause current_backlog >= @currentBacklog will not be evaluated.

NoviceToDotNet Over a year ago

Hello sir when i applied the idea explained by you above it's not working for null it's giving the sql exception Parameterized Query '(@courseId int,@passoutYear int,@currentBacklog int,@sex int,@eG' expects parameter @currentDegeePercentage, which was not supplied.

Guffa · Accepted Answer · 2010-08-16 13:28:36Z

1

You can test for a null value in the condition, and use the value from the table instead. Example:

... where course_id = isnull(@courseId, course_id) ...

answered Aug 16, 2010 at 13:28

Guffa

703k111 gold badges760 silver badges1k bronze badges

4 Comments

NoviceToDotNet Over a year ago

every time i need to check for not null parameter how can i do that? I am not getting would you please put some code

NoviceToDotNet Over a year ago

i am not using stored procedure i just want to know for simple parametrized query

NoviceToDotNet Over a year ago

u mean to say that every time i will have too check for null parameter value and then i will have to concatenate them

Guffa Over a year ago

@NoviceToDotNet: Just put the code in the query and the value in the parameter. If the value is null, the condition evaluates to course_id = course_id and you compare the field to itself, otherwise the condition evaluates to course_id = @courseId. As you can use the same code for both null and non-null values, you only need one version of the query regardless of which parameter values are null.

Beth · Accepted Answer · 2010-08-16 15:50:13Z

0

Instead of having

cmd = "one long string with many clauses, some of which are optional"

try

cmd = "first clause, required"
if second param is not null then
   cmd = cmd & "second clause, optional"

answered Aug 16, 2010 at 15:50

Beth

9,6171 gold badge26 silver badges44 bronze badges

Collectives™ on Stack Overflow

sql query to avoid null value parameter

3 Answers 3

5 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related