带有参数存储过程的条件查询

Question

我正在学习SQL，所以我还不知道语言的所有微妙之处

我编写了以下存储过程(此处简化(：

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

但这看起来并不是一种自然的方法，而且有很多重复的代码。

我想避免类似的事情

EXEC sp_executesql @sqlStrComplet

但如果这是唯一的办法。

我不知道正确的标签，但sqllocaldb info MSSQLLocalDB返回

版本：13.1.4001.0

我使用SQL Server Management Studio(SSMS(

Answer 1

只是：

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

where子句可以简化为：

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