How to pass null values into stored procedure via Entity Framework? entity-framework entity-framework-4 entity-framework-6 sql-server


I am using an ASP.NET MVC 4 application with Entity Framework.

I want to pass null values conditionally, let's say if pass value into DeptID then it should list of values for only passed DeptID, and if pass DeptID = NULL then it should it all the departments.

I wrote a procedure by checking DeptID and it returns the desired output (if DeptID is null, then all rows are returned).

But problem is to when I want to pass null values through following code of Entity Framework it is not working.

IdleTicketsChecker.Data.Test11Entities objModel = new Data.Test11Entities();
List<GeResult_Result> objTicketList =ExecuteCustomStoredProc<GetIdleTicketsByMinutes_Result>("GeResult", " @DeptID",
new SqlParameter("DeptID", DeptID)).ToList();

Any idea why it is so?

10/12/2015 12:13:01 PM

Accepted Answer

If DeptID is NULL, then you need to pass DBNull.Value into your SqlParameter

Try this:

IdleTicketsChecker.Data.Test11Entities objModel = new Data.Test11Entities();

// I'm *assuming* that DeptID is defined as an "int" - if not, adapt as needed
SqlParameter paramDeptId = new SqlParameter("DeptID", SqlDbType.Int);

if (DeptID == null)
    paramDeptId.Value = DBType.Null;
    paramDeptId.Value = DeptID;

List<GeResult_Result> objTicketList = ExecuteCustomStoredProc<GetIdleTicketsByMinutes_Result>("GeResult", "@DeptID", paramDeptId).ToList();
10/12/2015 12:14:46 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow