Click here to Skip to main content
15,845,681 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Friends,
I have
StudentMst table
with
SID Int as Student Id,
SName Varchar as Student Name,
Std Int as Standard,
Status Int as 1 or 2 which is Pass or Fail
as Columns

I created a search page ASP.NET to input number in a TextBox for "Standard" as number like 6 or 7 or 8 or 9 etc for 6th standard or 7th standard and so on.,
and display the result of all students in a gridview along with "Status" column that displays Status as 1 or 2 accordingly
but I want it as "Pass" or "Fail" directly instead of 1 or 2 in the gridview.

The Stored Procedure goes like this

CREATE PROCEDURE SearchStudent
@SID INT =NULL
,@SName VARCHAR(50)=NULL
,@Std INT =NULL
,@Status INT =NULL
AS
BEGIN
SELECT * FROM StudentMst
WHERE (@SID IS NULL OR SID = @SID)
AND (@SName IS NULL OR SName = @SName OR SName LIKE '%' + @SName + '%')
AND (@Std IS NULL OR Standard = @Std)
AND (@Status IS NULL OR Status = @Status)
END

I dont want to use SQL directly in C# in string query, SqlDataAdapter(query, conn) bla bla bla
I guess we can use CASE WHEN THEN ELSE statements
I dont know how to use it in Stored Procedure.

Please help
Thanks
Ravi

What I have tried:

CREATE PROCEDURE SearchStudent
@SID INT =NULL
,@SName VARCHAR(50)=NULL
,@Std INT =NULL
,@Status INT =NULL
AS
BEGIN
SELECT * FROM StudentMst
WHERE (@SID IS NULL OR SID = @SID)
AND (@SName IS NULL OR SName = @SName OR SName LIKE '%' + @SName + '%')
AND (@Std IS NULL OR Standard = @Std)
AND (@Status IS NULL OR Status = @Status)
END
Posted
Updated 20-Aug-22 22:32pm

1 solution

Try something like this:
SQL
SELECT SName, 
       CASE WHEN Std = @GradeYouWantToMatch 
            THEN 'Passed' 
            ELSE 'Failed' 
       END AS Status 
FROM MyTable
And pass your grade as the parameter @GradeYouWantToMatch
 
Share this answer
 
Comments
ravitv 21-Aug-22 3:39am    
Thanks OriginalGriff,
but where can I use this
CASE WHEN Std = @GradeYouWantToMatch.........
in the Stored Procedure. I have no idea.
Can you please give me a clue
ravitv 21-Aug-22 3:59am    
Oh...I figured it out OriginalGriff, thanks..
I forgot to include all the column names.
Excellent OriginalGriff,
thanks again.
Ravi
OriginalGriff 21-Aug-22 7:49am    
You're welcome!
ravitv 21-Aug-22 4:18am    
Hi OriginalGriff,
here is another situation to include "Discontinued" when status=3.
How can I write the SQL please.
Thanks
Ravi
OriginalGriff 21-Aug-22 7:50am    
Oh come on - it you have the code for one, the code for the other is pretty much identical!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900