Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005
    Posts
    74

    Unanswered: Help with CASE in Stored Procedure

    hi guys! Help please..I'm new to sotred procedure. Currently, i have a stored procedure (see Below) but i can't figure out on how to solve the errors(see below). What im trying to do with my stored procedure is to take two(2) parameters, the string to be search(@Search_Arg) and the search by(@Search_by) Either by Login name or by firstname. Under my CASE statement, When the @Search_by is equal to Username i want to select the user with User_login equals to @Search_Arg and when the @Search_by is equal to Firstname it will select all user with User_FirstName equals to @Search_Arg...Any help please..Thanks in advance!

    Code:
    CREATE PROCEDURE sp_User_Search
    	@Search_Arg varchar(50),
    	@Search_By varchar(20)
    AS
    BEGIN
    	
    	CASE @Search_By
    		WHEN 'Username' THEN SELECT * FROM RCPS_UserAccount WHERE User_login = @Search_Arg 
    		WHEN 'Firstname' THEN SELECT * FROM RCPS_UserAccount WHERE User_FirstName = @Search_Arg		
    	END
    END

    Msg 156, Level 15, State 1, Procedure sp_User_Search, Line 7
    Incorrect syntax near the keyword 'CASE'.
    Msg 156, Level 15, State 1, Procedure sp_User_Search, Line 9
    Incorrect syntax near the keyword 'WHEN'.
    Msg 156, Level 15, State 1, Procedure sp_User_Search, Line 10
    Incorrect syntax near the keyword 'END'.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    what you have there is a dynamic search condition. read this for various techniques:

    http://www.sommarskog.se/dyn-search.html

    in particular, this might suit you:

    select col1, col2, col3 from RCPS_UserAccount
    where
    (User_login = @user_login or @user_login is null) and
    (User_FirstName = @user_firstname or @user_firstname is null)

  3. #3
    Join Date
    May 2007
    Posts
    49
    Case can return only one value at a time
    use of 'Select *' is not allowed in case statement. What you can do is :

    HTML Code:
    CREATE PROCEDURE sp_User_Search
    	@Search_Arg varchar(50),
    	@Search_By varchar(20)
    AS
    BEGIN
    		SELECT * FROM RCPS_UserAccount 
    		WHERE 
    		CASE 
    			When @Search_By = 'Username' then
    				User_login 		
    			When @Search_By = 'Firstname' then
    				User_FirstName
    			End
    		= @Search_Arg
    	END
    END
    ...but I think way suggested by jezemine is better than this one.
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •