HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Stored Procedure null parameter within where clause

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
storednullwhereprocedurewithinclauseparameter

Problem

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.

Solution

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.

Code Snippets

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

Context

StackExchange Database Administrators Q#74461, answer score: 16

Revisions (0)

No revisions yet.