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.