0

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..

2
Contributors
1
Reply
14
Views
3 Years
Discussion Span
Last Post by h4ng4r18
0

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!)

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.