CODEWITHSUNDEEP
c#sql-serverado.net
Dan Hermann
Dan Hermann

Reputation: 1107

How do I pass a DBNull value to a parameterized SELECT statement?

I have a SQL statement in C# (.NET Framework 4 running against SQL Server 2k8) that looks like this:

SELECT [Column1] FROM [Table1] WHERE [Column2] = @Column2

The above query works fine with the following ADO.NET code:

DbParameter parm = Factory.CreateDbParameter();
parm.Value = "SomeValue";
parm.ParameterName = "@Column2";
//etc...

This query returns zero rows, though, if I assign DBNull.Value to the DbParameter's Value member even if there are null values in Column2. If I change the query to accommodate the null test specifically:

SELECT [Column1] FROM [Table1] WHERE [Column2] IS @Column2

I get an "Incorrect syntax near '@Column2'" exception at runtime. Is there no way that I can use null or DBNull as a parameter in the WHERE clause of a SELECT statement?

Upvotes: 1

Views: 6498

Answers (5)

Joel Coehoorn
Joel Coehoorn

Reputation: 416053

Change your second line to this:

parm.Value = DbNull.Value;

That's all you have to do, really. Don't worry about the = vs IS in this instance.

Where I'm at, our homegrown data access tier that we share among projects also checks all query parameters automatically and replaces any C# null with DbNull.Value before executing the query.

Upvotes: -2

Koen
Koen

Reputation: 2571

Are you sure that they the values are NULL and not just empty strings? If it's the latter, it would indeed not return any record. And as Joel stated in his answer, yoou should allways change your null to DbNull.Value in your C#-code. If it's the first you should compare to '' instead.

If the data is indeed NULL, maybe you can try this statement (if you're using stored procedures):

SET ANSI_NULLS OFF

SELECT [Column1] FROM [Table1] WHERE [Column2] = NULL

Take a look at the folowing article for some extra insight.

Upvotes: 0

Crimsonland
Crimsonland

Reputation: 2204

You can use:

var cmd =new SqlCommand("SELECT ISNULL([Column1],'VALUE') as [Column1]  FROM [Table1] WHERE [Column2] = @Column2"),
                            con) 

string a;
if(textbox.text == string.empty)
{ a = DbNull.Value}
else { a =textbox.text}

cmd.Parameters.AddWithValue("@Column2",a);
cmd.ExecuteNonQuery();

regards!

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453658

SELECT [Column1] 
FROM [Table1] 
WHERE [Column2] = @Column2  OR (@Column2 IS NULL AND [Column2] IS NULL)

But I'm not sure how sargable this is.

Edit: This looks fine actually and ends up with an index seek at least in 2008

CREATE TABLE [Table1]
(
[Column1] INT,
[Column2] INT 
)

CREATE CLUSTERED INDEX [IX] ON [dbo].[Table1] ([Column2] ASC)

INSERT INTO [Table1] VALUES(1,NULL)
INSERT INTO [Table1] VALUES(2,NULL)
INSERT INTO [Table1] VALUES(3,1)
INSERT INTO [Table1] VALUES(4,1)
GO

CREATE PROC foo
@Column2 INT
AS
SELECT [Column1] 
FROM [Table1] 
WHERE [Column2] = @Column2  OR (@Column2 IS NULL AND [Column2] IS NULL)

GO

EXEC foo NULL
EXEC foo 1

Plan

Upvotes: 4

genericuser
genericuser

Reputation: 1450

you may use

SELECT [Column1] FROM [Table1] WHERE [Column2] = ISNULL(@Column2 , 'value');

'value' can be any value that u want it to represent. If you want to match column2 that has null columns,

SELECT [Column1] FROM [Table1] WHERE ISNULL([Column2], 'value') = ISNULL(@Column2 , 'value');

Upvotes: 2

Related Questions