Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2005
    Posts
    55

    Unanswered: stored procedure query question.

    ok, i have the following query which runs as a stored proc...

    Code:
    ALTER PROCEDURE [dbo].[sp_SelectDocumentBySearch]
    
    @searchString varchar(255)
    
    AS
    BEGIN
    
    SELECT document_id, document_code, document_title, document_category_id, 
    document_description, document_date_added, document_date_revision, 
    document_last_revised, document_author, document_version, document_level,
    category_id, category_title
    
    FROM tbl_documents
    
    JOIN tbl_categories ON document_category_id = category_id
    
    WHERE document_level = '3' AND document_code LIKE @searchString OR document_title LIKE @searchString
    OR document_description LIKE @searchString OR category_title LIKE @searchString
    
    
    END
    in my .net app, its a search function, u make the @searchString parameter to be %string% (as a search feature to find anything with 'string')...now, when there is a string present, it works correctly by displaying results only with document_level = '3'....however, when there is no string, that is just a %% as the @searchString, it returns all results, regardless of what document_level is...

    but i want to keep it for a wildcard search, yet still retain document_level = '3' statement. is this possible?

    Cheers, Justin

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I think you need to change your where clause to add some parens:

    Code:
    WHERE 
      document_level = '3' AND 
      (
       document_code LIKE @searchString OR 
       document_title LIKE @searchString OR 
       document_description LIKE @searchString OR 
       category_title LIKE @searchString
      )

  3. #3
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Just use a IF condition to seperate that...check @searchString=' '
    then execute
    Code:
    SELECT document_id, document_code, document_title, document_category_id, 
    document_description, document_date_added, document_date_revision, 
    document_last_revised, document_author, document_version, document_level,
    category_id, category_title
    
    FROM tbl_documents
    
    JOIN tbl_categories ON document_category_id = category_id
    
    WHERE document_level = '3'
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

Posting Permissions

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