Stored Procedure null parameter within where clause
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.
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