How to use IF within AND in T-SQL

Question

I am trying to create a stored procedure which will use one of the optional parameters provided. In the code below they are @nStudentId and @nStudentIds set to NULL initially. Only one of them will be sent when the proc is called. When @nStudentIds are sent they will come-in as comma separated values.

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

So when @nStudentId is sent it should be this in the AND

SR.StudentId = @nStudentId

When @nStudentIds is available I can use 'IN' like so:

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

The limitation of my knowledge of SQL shows in this IF, which obviously does not work:

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,','))                       
        )

Any suggestions are appreciated. Thanks in advance.

Regards.

You don't. `IF` is a logical flow operator not a function. Stick to boolean logic (`AND`s and `OR`s). — Thom A, Mar 05 '21 at 10:25
Why use 2 different parameters though? Why not *just* use `@nStudentIds`? It can contain a single `ID`. Or, better yet, use a table type parameter and `JOIN` to it. — Thom A, Mar 05 '21 at 10:30
Stop splattering your code with [nolock](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) — SMor, Mar 05 '21 at 13:03

score 3 · Answer 1 · answered Mar 05 '21 at 10:36

3

There's literally no need for 2 parameters here. Ideally, what you should be using is a table type parameter, as that maintains the strong typing. Then you can just JOIN to said table type parameter:

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

Then you would call the procedure with something like:

DECLARE @ProfessorId bigint, @Students dbo.IDs;

SET @ProfessorId = 123456789;
INSERT INTO @Students (ID)
VALUES(987654321),(5643321987342);

EXEC dbo.usp_GetStudentReferrals @ProfessorId, @Students;

answered Mar 05 '21 at 10:36

Thom A

88,727
11
45
75

This is the answer. For someone who did not encounter TYPEs yet, it might be helpful to know that the TYPE is just another construct like tables and stored procedures and will be stored permanently in the database as well (under Programmability->Types->User-Defined Table Types). – Valentin Sky Mar 05 '21 at 11:18
One option if you sometimes can't be bothered messing about with loading up a TVP, is to `union all` the single parameter (when it is not null) with the TVP (which always exists, just might be empty) – Charlieface Mar 05 '21 at 11:39
@Larnu, your opinion is valid if it were a design change. Changing method signatures, as per your suggestion, has impact on current usage. I also have to think about backwards compatibility. Thank you for your thoughts though. – Codehelp Mar 05 '21 at 13:58
With respect, it's not an "opinion"; the fact that you have 2 parameters for the same thing is a design flaw and 2 aren't required. – Thom A Mar 05 '21 at 14:14

gunhal · Answer 2 · 2021-03-05T12:06:51.377

0

why don't you put IF before SELECT

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

you can using split string from link

and change this part

AND (if(@nStudentId <> 0 AND @nStudentId <> -1 AND @nStudentId IS NULL) SR.StudentId = @nStudentId; else if(@nStudentIds IS NOT NULL) SR.StudentId IN (SELECT name FROM dbo.splitstring(@nStudentIds))
)

edited Mar 05 '21 at 12:06

answered Mar 05 '21 at 10:42

gunhal

11
2

There's a lot more to this proc than was posted which was removed for brevity like other joins and more AND conditions. The one in question is where the help is requested. – Codehelp Mar 05 '21 at 10:48

How to use IF within AND in T-SQL

2 Answers2