I'm wondering if I can use the case statement in a stored procedure for the where clause.

Example: In my vfp form, I want to let the user decide which field to filter:

I have a combo box for User, and a combo box for action. Each combo box will have the available section with ALL being an option also.

I was wondering if I could create one stored procedure that was something like

CREATE PROCEDURE ct_UserAuditListing 
@tiUserID int,
@tcAction varchar(30)

--tiUserID=0 we want all users
--tcAction="ALL" we want all actions

as

select * from coAudit
case 
	when @tiUserid>0 and @tcAction='ALL' then where userid=@tiUserID 
	when @tiUserID>0  then where userid=@tiUserID and actiontype=@tcAction 
	when @tiUserID=0 and @tcAction<>'ALL' then where actiontype=@tcAction 

Obviously, the code above doesn't work, but is there a way to make it work or do I have to make a seperate stored procedure for each flavor of the query

Kirk