Stored Procedure null parameter within where clause

Question

I want to use a parameter within where clause only if it's value is provided by strongly typed dataset, this is what I am trying at the moment, I get right results when I provide parameter3 and no results when I don't provide it's value.

What I desire is when I provide no value for parameter3, it should not use it in the query, as it's value is null, and I want to see all of the results in the query, not where Paramerter3 = null ones:

ALTER procedure [dbo].[GetData]
(
 @Parameter1 varchar(256),
 @Parameter2 varchar(256),
 @Parameter3 int = null
)
AS
SELECT
 *
FROM
 Table1
WHERE
 Table1.URL LIKE '%' + @Parameter1 + '%' 
 AND Table1.ID = @Parameter2
 AND (@Parameter3 IS NULL OR Table1.ID2 = @Parameter3)
ORDER BY
 Table1.Title

Edit: I tried Thomas answer and executed like this:

EXEC @return_value = [dbo].[GetData]
 @Parameter1 = N'asda',
 @Parameter2 = N'asda',
 @Parameter3 = null
SELECT 'Return Value' = @return_value
GO

I also updated the stored procedure as Thomas said.

• Lamak: Well, are there actually any rows where URL LIKE '%asda%' AND ID = 'asda' in your table?
• Lamak: Here is an sqlffidle with sample data showing that the current answer should actually work
• Mathematics: sorry, It was my fault. @Lamak yes it works. Thank You :-)

Answer 1

SELECT *
FROM Table1
WHERE Table1.URL LIKE '%' + @Parameter1 + '%' AND Table1.ID = @Parameter2
AND 
(
 @Parameter3 is null 
 or Table1.ID2 = @Parameter3
);

Take a look at the above example. If you change your AND clause to a nested OR clause specifying your initial expression as well as @Parameter3 is null. That will then demand that the nested expression is true if @Parameter3 is NULL.

• ConcernedOfTunbridgeWells: You can also inline this sort of thing with and isnull (@Parameter3, Table1.ID2) = Table1.ID2, although the query optimiser won't get much more joy from it.
• swasheck: or use COALESCE if you like SQL Standards and all that
• Thomas Stringer: Don't do @param = null. You need to do @param is null. Nothing can be "equal" to NULL.
• Jephren Naicker: The non - dynamic SQL Approach

Answer 2

I've always been a fan of a dynamic sql approach for this type of problem. I find it provides the optimal balance between complexity versus quality query plan.

In the following code, I define a base query which does whatever it would need to do and then only add in the filters if the provided parameter is not null.

CREATE PROCEDURE [dbo].[GetData]
(
 @Parameter1 varchar(256),
 @Parameter2 varchar(256),
 @Parameter3 int = null
)
AS
BEGIN
 SET NOCOUNT ON;
 DECLARE 
 @BaseQuery nvarchar(max) = N'SELECT T.* FROM dbo.Table1 AS T'
 , @ParamList nvarchar(max) = N'@p1 varchar(256), @p2 varchar(256), @p3 int'
 , @WhereClause nvarchar(max) = ' WHERE 1=1';
 IF @Parameter1 IS NOT NULL
 BEGIN
 SET @WhereClause = @WhereClause + ' AND T.Url = @p1';
 END
 IF @Parameter2 IS NOT NULL
 BEGIN
 SET @WhereClause = @WhereClause + ' AND T.ID = @p2';
 END
 IF @Parameter3 IS NOT NULL
 BEGIN
 SET @WhereClause = @WhereClause + ' AND T.ID2 = @p3';
 END
 SET @BaseQuery = @BaseQuery + @WhereClause;
 EXECUTE sp_executesql @BaseQuery, @ParamList, @p1 = @Parameter1, @p2 = @Parameter2, @p3 = @Parameter3;
END

• billinkc: Hey Downvoter, if you have any feedback on how I can make this not-useful answer into a useful one, I'm all ears

Answer 3

It actually can be much easier:

SELECT *
FROM Table1
WHERE Table1.URL LIKE '%' + @Parameter1 + '%' AND Table1.ID = @Parameter2
 AND Table1.ID2 = ISNULL(@Parameter3, Table1.ID2)

Note: I didn't see the comment of ConcernedOfTunbridgeWells when I wrote the answer. I deleted my answer when I found out. But then on the 2nd thought, I might just keep it and hope it may help others. "Where @parameter is null or ... " doesn't quite make sense although it might work. Using dynamic SQL is overkill. I use the following code for MS SQL and Sybase quite often to check whether a parameter is null or empty:

SELECT * 
FROM tblName 
WHERE [ColumnName] = ISNULL(NULLIF(@parameter, ''), [ColumnName])
 AND ('something else here')