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