CODEWITHSUNDEEP
sql-serverlinqstored-procedurescrystal-reports
PhamMinh
PhamMinh

Reputation: 2665

Problem with Nullable field in Linq stored procedure + crystal reports on VS2008

I have a table in Sql Server that contain an int field price that allow nulls. I have created the following stored procedure:

CREATE PROCEDURE [dbo].[proc_test]
AS
    select a.id, a.product, case when a.price is null then 0 else a.price end
    from tblOne a
    ...

I have also created a linq to sql file that uses proc_test.

The problem is that auto generated linq to sql code in: designer.cs has a nullable price variable and Crystal Reports dosen't allow nullable variables.

PS: I'm not allowed to change the structure of tblOne.

Upvotes: 0

Views: 481

Answers (2)

Adrian Toman
Adrian Toman

Reputation: 11486

Use the IsNull() function instead of the case expression. Therefore change your stored procedure to be:

CREATE PROCEDURE [dbo].[proc_test]
AS
    select a.id, a.product, isnull(a.price, 0)
    from tblOne a
    ...

Note:

Coalesce is not suitable in this situation:

An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL. In SQL Server

http://msdn.microsoft.com/en-us/library/ms190349.aspx

Upvotes: 1

Juan Ayala
Juan Ayala

Reputation: 3518

Can you manually change the mapping in the linq to sql file so that it is not null. The SP will never return null anyway. The file is a dbml right?

I don't even know if thats possible. I know it would if it were linq to entities.

Upvotes: 0

Related Questions