patternsqlModerate
Stored Procedure null parameter within where clause
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
What I desire is when I provide no value for
Edit: I tried Thomas answer and executed like this:
I also updated the stored procedure as Thomas said.
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.TitleEdit: 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
GOI 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.