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

Question

I have an SQL statement in C # (.NET Framework 4 working with 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 null rows, though if I assign DBNull.Value to the DbParameter Value member, even if column 2 has null values. If I changed the query to specifically set the null test:

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

I get "Invalid syntax near exception @@ Column2" at runtime. Can't I use null or DBNull as a parameter in a WHERE clause of a SELECT statement?

Answer 1

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

'value' , u . 2 ,

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

Answer 2

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

, .

: , , 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

Answer 3

:

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();

!

Answer 4

, NULL, ? , . , null DbNull.Value # -. , ''.

NULL, , ( ):

SET ANSI_NULLS OFF

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

article .

Answer 5

:

parm.Value = DbNull.Value;

, , . = IS .

, , , # null DbNull.Value .