Return all row when the stored proc's parameter is null 1

[Gwena]

Hi guys,
I'm new at SQL Server and was wondering if you can help me with this one.
I have a simple SP that have one param.
What I would like it do to is that when I'm not sending any value to this param, the SP will return all rows. The ones that are null and the one that are not. Is this possible?

This is my SP

CREATE PROCEDURE usp_Get_All_Patent_Actions_Report
@file_no nvarchar(100) = NULL

AS

SELECT
[File].file_no, [File].Note

FROM
[File]

WHERE ([File].main_type = 1) AND

[File].file_no=@file_no

 

[bborissov]

CREATE PROCEDURE usp_Get_All_Patent_Actions_Report
    @file_no nvarchar(100) = NULL
AS    
SELECT
    [File].file_no, [File].Note
FROM
    [File]
WHERE CASE WHEN @file_no IS NULL THEN 1=1
           ELSE  ([File].main_type = 1) AND
                  [File].file_no=@file_no
           END

Borislav Borissov

 

[Gwena]

Hi bborissov,
Thanks for answer me so quickly.
I have another Question.
Suppose I have 2 params that only when both of them are null then return all rows.

Thanks

 

[bborissov]

Change WHERE clause:

WHERE CASE WHEN @file_no IS NULL AND @sec_par IS NULL THEN 1=1
           ELSE  ([File].main_type = 1) AND
                  [File].file_no=@file_no
           END

Borislav Borissov

 

[Gwena]

Took me a while but I got it
Thanks bborissov