[RESOLVED] Null Value into SQL Para Query

[BaylessK]

Is there syntax to pass a null value into a MS SQL 2000 database query set up for a parameter value so that the query will disregard the parameter? I've encountered this situation with both ASP 2.0 dataset added to a web site and a VB.net data adapter filling a dataset. With a query using multiple filter criteria using the =@ParamName within the query I hope there is syntax to tell the query to disregard one or more filter criteria not used. Otherwise I'll need a large combination of queries to handle all possible combinations of filter items selected. If yes, I'd love to see a code snippet. Thanks.

[brucevde]

I write the Where clause using something like

(@Param1 Is Null Or Field1 = @Param1)  And
(@Param2 Is Null Or Field2 = @Param2)  And
(@Param3 Is Null Or Field3 = @Param3)

[BaylessK]

Here is a bit more info.
The SQL for the query looks like this:
SELECT int4FilingRecordNumber, int4DocketRecordId, int4DocketFilingNumber, dtm8WhenFiled, int1FilingTypeNumber
FROM tblFiledDocument
WHERE (int1FilingTypeNumber = @FilingType) AND (int4DocketRecordId = @DocketRecordId)

The vb code from the ASPX.VB partial class looks like this:
Dim dadapter As New tblFiledDocumentTableAdapter
DataGrid1.DataSource = dadapter.GetDataByFilingType(FilingType, DocketRecord)
DataGrid1.DataBind()
DataGrid1.Visible = True

This method of binding for the control works great as long as you supply an integer for the two parameter fields. The question is how to allow the parameter query to work if the user wants to filter only by one of the fields which means the other field essentially wants a * passed into a int field.

[Mark Gambo]

I found THIS and it seems to work me.

[BaylessK]

Thanks much for the response. The COALESCE is required at the SP to let the query disregard null inbound parameters and the variable must be dimensioned in code as nullable. Here is code snippet:
In SP properties:
WHERE int1FilingTypeNumber = COALESCE(@int1FilingTypeID, int1FilingTypeNumber)
AND int1DocketTypeID = COALESCE(@int1DocketTypeID, int1DocketTypeID)

In ASP.NET code: Dim FilingType As Nullable(Of Integer) = Nothing
If lstFilingType.SelectedValue.ToString <> Nothing Then
FilingType = lstFilingType.SelectedValue.ToString
End If