Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Well, are there actually any rows where URL LIKE '%asda%' AND ID = 'asda' in your table? –  Lamak Aug 20 at 16:19
1  
Sorry, this really isn't making much sense. Can you post a sqlfiddle with a sample data for your table? –  Lamak Aug 20 at 16:23
    
Here is an sqlffidle with sample data showing that the current answer should actually work –  Lamak Aug 20 at 16:30
    
sorry, It was my fault. @Lamak yes it works. Thank You :-) –  Customized Name Aug 20 at 16:31

2 Answers 2

up vote 6 down vote accepted
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.

share|improve this answer
1  
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. –  ConcernedOfTunbridgeWells Aug 20 at 16:08
2  
or use COALESCE if you like SQL Standards and all that –  swasheck Aug 20 at 16:09
2  
Don't do @param = null. You need to do @param is null. Nothing can be "equal" to NULL. –  Thomas Stringer Aug 20 at 16:12

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
share|improve this answer
    
Thank you, +1, I couldn't try it but it seems like it should work too –  Customized Name Aug 20 at 16:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.