I'm learning SQL so I don't know yet all the subtlety of the language, I wrote the following stored procedure (simplified here): But it doesn't look like a natural way to do that and there is a lot of repeated code. I want to avoid something like But if it's the only way. I don't know the correct tag but

Conditional query with a parameter stored procedure

I’m learning SQL so I don’t know yet all the subtlety of the language,

I wrote the following stored procedure (simplified here):

ALTER PROCEDURE [dbo].[SelectAllIssues]
    @Status nvarchar(1) = 0
AS
BEGIN
    SET NOCOUNT ON;

    IF @Status = 1 OR @Status = 2
    BEGIN
        SELECT IssueStatuses.Id AS 'StatusId'
        FROM Issues 
        INNER JOIN IssueStatuses ON Issues.IssueStatusId = IssueStatuses.Id
        WHERE Issues.IssueStatusId = @Status
        ORDER BY Created
    END
    ELSE
    BEGIN
        SELECT IssueStatuses.Id AS 'StatusId'
        FROM Issues 
        INNER JOIN IssueStatuses ON Issues.IssueStatusId = IssueStatuses.Id
        ORDER BY Created
    END 
END

But it doesn’t look like a natural way to do that and there is a lot of repeated code.

I want to avoid something like

EXEC sp_executesql @sqlStrComplet

But if it’s the only way.

I don’t know the correct tag but sqllocaldb info MSSQLLocalDB return

Version : 13.1.4001.0

and I use SQL Server Management Studio (SSMS)

Answer

Just:

select s.Id as StatusId
from issues i
inner join IssueStatuses s on i.IssueStatusId = s.Id
where (i.IssueStatusId = @Status and @status in (1, 2)) or @status not in (1, 2)
Order By created

The where clause can be simplified:

where i.IssueStatusId = @Status or @status not in (1, 2)

Advertisement

Answer