Parameter: C, I, P, Or ALL...how To Select Based On Parameter?

Hi everyone,

I have this semi-complex query that is selecting items from numerous tables residing on 2 different databases. So far the query works perfectly. Here is the problem: The user is given the option of selecting items based on whether a course is Completed (C) Incomplete (I) Passed (P) Failed (F) or (ALL). I am not really sure how to do the select all, the others I can do depending on the value...

Any thoughts??

Query:


sql Code:






Original
- sql Code





ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser]
(
@status varchar(25),
@course varchar(100),
@datesmalldatetime
)
AS

SELECT
A1.uLastName,
A1.uFirstName,
A2.mName,
A3.tStatus,
A3.tScore,
A3.tStartDate,
A3.tCompleteDate,
A4.cName

FROM VSALCP.dbo.[User] as A1
INNER JOIN FSDLMS.dbo.Student as A5
ON A1.uID = A5.stID

INNER JOIN FSDLMS.dbo.Transcript as A3
ON A3.t_FK_stID = A5.stID

INNER JOIN VSALCP.dbo.Member as A2
ON A2.mID = A1.u_FK_mID

INNER JOIN FSDLMS.dbo.Course as A4
ON A4.cID = A3.t_FK_cID

Where @status = A3.tStatus ...






 ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser]    (        @status varchar(25),        @course varchar(100),        @date   smalldatetime    )AS   SELECT    A1.uLastName,    A1.uFirstName,     A2.mName,    A3.tStatus,    A3.tScore,    A3.tStartDate,    A3.tCompleteDate,    A4.cName     FROM VSALCP.dbo.[User] AS A1    INNER JOIN FSDLMS.dbo.Student AS A5    ON A1.uID = A5.stID     INNER JOIN FSDLMS.dbo.Transcript AS A3    ON A3.t_FK_stID = A5.stID     INNER JOIN VSALCP.dbo.Member AS A2    ON A2.mID = A1.u_FK_mID      INNER JOIN FSDLMS.dbo.Course AS A4    ON A4.cID = A3.t_FK_cID WHERE @status = A3.tStatus ...


"If status = ALL select * status"

Thanks for taking a look!