Case When inside where clause

[newbie26]

please help with the query..I want to have a conditional statement in where clause
depending on the result of select Count(*) statement

originally my query is:

select * from StudentMaster where StudentMaster.StudId = @pStudId and exists (select * from tblFinals where tblFinals.StudId=StudentMaster.StudId)

but just today,
I realize that user will have the option to select students either in tblFinals or in tblSemiFinals

so the condition will be:

if (select count(*) from tblStudentGrades where StudId=@pStudId and Remarks='Finals') > 0

then
    select * from StudentMaster where StudentMaster.StudId = @pStudId and exists (select * from tblFinals where tblFinals.StudId=StudentMaster.StudId)

else
    select * from StudentMaster where StudentMaster.StudId = @pStudId and exists (select * from tblFinals where tblSemiFinals.StudId=StudentMaster.StudId)

but i want the first part of the query to be permanent and will just change in where clause like this:

select * from StudentMaster where StudentMaster.StudId = @pStudId and 

if (select count(*) from tblStudentGrades where StudId=StudentMaster.StudId and Remarks='Finals') > 0

    then
        exists (select * from tblFinals where tblFinals.StudId=StudentMaster.StudId)
    else
        exists (select * from tblFinals where tblSemiFinals.StudId=StudentMaster.StudId)

is this possible??thanks..

[h4ng4r18]

Dynamic Sql

This mightn't be the 'best' way to do this - but certainly the easiest to understand.

You are creating a statement conditionally as string:

DECLARE 
@Statement nvarchar(max)

SELECT @Statement = 'select * from StudentMaster where StudentMaster.StudId = ' + @pStudId + ' and '

IF EXISTS (select count(1) from tblStudentGrades where StudId=StudentMaster.StudId and Remarks='Finals')
    BEGIN
        SELECT @Statement = @Statement + 'exists (select * from tblFinals where tblFinals.StudId=StudentMaster.StudId)'
    END
ELSE
    BEGIN
        SELECT @Statement = @Statement + 'exists (select * from tblFinals where tblSemiFinals.StudId=StudentMaster.StudId)'
    END

exec sp_executesql @Statement

and then executing it at the end as a parameter to the sp_executesql stored procedure.

It is likely you may need to convert to integer id:
' + @pStudId + '

to varchar
' + CAST(@pStudId as varchar(5)) + '

(This is untested!)