I’m learning SQL so I don’t know yet all the subtlety of the language,
I wrote the following stored procedure (simplified here):
x
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)
Advertisement
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)