CODEWITHSUNDEEP
c#sql
NoviceToDotNet
NoviceToDotNet

Reputation: 10805

How to short curcit null parameter (SQL)

Hello Friends i have the following query which defeat the very purpose.

A day ago i asked suggestion for to sort circuit the parameter which are null for that i got the suggestion to use
(@firstYrPercent is null OR first_year_percent>=@firstYrPercent) like this to deny null parameter but today when i am running actual query i think that above mentioned idea is of no use.

I am getting sql exception for all those parameter which are null and it is demanding value for those error is this Sql Exception is caught:

"Parameterized Query '(@courseId int,@passoutYear int,@currentBacklog int,@sex int,@eG' expects parameter @currentDegeePercentage, which was not supplied. "

Here is the query please suggest me an alternative:

string cmd = @"SELECT * FROM [tbl_students] WHERE course_id=@courseId 
          AND  branch_id IN(" + branchId + @") 
          AND (@firstYrPercent is null OR first_year_percent>=@firstYrPercent)
          AND (@secondYrpercent is null OR second_year_percent>=@secondYrPercent)
          AND (@thirdYrPercent is null OR third_year_percent>=@thirdYrPercent)
          AND (@finalYearpercent is null OR final_year_percent>=@finalYearpercent)
          AND (@currentDegeePercentage is null OR current_degree_percent>=@currentDegeePercentage)
          AND (@passoutYear is null OR passing_year>=@passoutYear) 
          AND (@currentBacklog is null OR current_backlog<=@currentBacklog)
          AND gender=@sex 
          AND (@eGap is null OR gapin_education<=@eGap)
          AND (@highSchoolPercentge is null OR highschool_percentage>=@highSchoolPercentge)
          AND (@higherSchoolPercentage is null OR ssc_percentage>=@higherSchoolPercentage)
          AND (@grauationPercentage is null OR graduation_percentage>=@grauationPercentage)
          AND (@diplomaPercentage is null OR diploma_percentage>=@diplomaPercentage)
          AND (@noOfAtkt is null OR number_of_ATKT<=@noOfAtkt)
          AND (@date is null OR DOB>=@date)";

Upvotes: 0

Views: 254

Answers (2)

AdaTheDev
AdaTheDev

Reputation: 147224

You need to make sure you define the @currentDegeePercentage parameter on the Command you are executing - specifically, if you want to pass in that parameter as null, ensure the parameter is added to the command with a value of: DBNull.Value.

If you don't add the parameter to the Command.Parameters collection, or add it but set the value to null (as opposed to DBNull.Value), then you will get the error you are seeing.

Upvotes: 1

Aaron Digulla
Aaron Digulla

Reputation: 328556

The problem is not that the parameter is null but that the parameter is missing. Check for typos (it's "degree", not "degee" [sic]).

Upvotes: 0

Related Questions