如何在T-SQL AND中使用IF

Question

我正在尝试创建一个存储过程，它将使用提供的可选参数之一。在下面的代码中，它们是@ nstudententid和@ nstudententids，最初设置为NULL。当调用该进程时，只会发送其中一个。当@ nstudententids被发送时，它们将以逗号分隔的值进入。

CREATE PROCEDURE [dbo].[usp_GetStudentReferrals] 
(
@ProfessorId BIGINT,
@nStudentId BIGINT = NULL, 
@nStudentIds NVARCHAR(999) = NULL

)
AS
BEGIN
SELECT DISTINCT SR.StudentReferralId
FROM StudentReferral SR WITH(NOLOCK)
INNER JOIN PotentialCandidate PC WITH(NOLOCK) ON PC.PotentialCandidateId = SR.StudentId
WHERE SR.ProfessorId = @nProfessorId
AND -- this is where I am not able to figure out the logic to use either @nStudentId or @nStudentIds, whichever is passed in.
END 

所以当@ nstudententid被发送时它应该是这个在AND

SR.StudentId = @nStudentId 

当@ nstudententids可用时，我可以像这样使用'IN':

SR.StudentId IN (SELECT value FROM STRING_SPLIT @nStudentIds, ','))

我的SQL知识的局限性显示在这个IF中，它显然不起作用:

AND (if(@nStudentId <> 0 AND @nStudentId <> -1 AND @nStudentId IS NULL)
SR.StudentId = @nStudentId;
else if(@nStudentIds IS NOT NULL)
SR.StudentId IN (SELECT value FROM STRING_SPLIT@nStudentIds,','))                       
)

欢迎提出任何建议。提前谢谢。

致意。

Answer 1

这里实际上不需要两个参数。理想情况下，您应该使用表类型参数，因为这样可以维护强类型。然后你可以只输入JOIN到表类型参数

CREATE TYPE dbo.IDs AS table (ID bigint);
GO

CREATE PROC dbo.usp_GetStudentReferrals @ProfessorId bigint, @StudentIDs dbo.IDs READONLY AS
BEGIN
SELECT DISTINCT SR.StudentReferralId
FROM dbo.StudentReferral SR --WITH(NOLOCK) --Why are you using NOLOCK? You do know what it does, right?
INNER JOIN dbo.PotentialCandidate PC /*WITH(NOLOCK)*/ ON PC.PotentialCandidateId = SR.StudentId --Why are you using NOLOCK? You do know what it does, right?
INNER JOIN @StudentIDs S ON SR.StudentID = S.ID
WHERE SR.ProfessorId = @ProfessorId; --I assumed this should be @ProfessorId not @nProfessorId
END;
GO

然后你可以这样调用这个过程:

DECLARE @ProfessorId bigint, @Students dbo.IDs;
SET @ProfessorId = 123456789;
INSERT INTO @Students (ID)
VALUES(987654321),(5643321987342);
EXEC dbo.usp_GetStudentReferrals @ProfessorId, @Students;

Answer 2

为什么不在SELECT前加上IF

BEGIN
if(@nStudentId <> 0 AND @nStudentId <> -1 AND @nStudentId IS NULL)
SELECT DISTINCT .....
else
SELECT DISTINCT .....

可以使用拆分字符串from链接

并更改这部分

AND (if(@nStudentId <>)0 AND @nStudentId <>-1和@ nstudententid是NULL)sr . studententid = @ nstudententid;else if(@ nstudententids IS NOT NULL)sr . studententid IN (SELECT name FROM dbo.splitstring(@ nstudententids))
)