locked
How can I building search ALL using text box? RRS feed

  • Question

  • User2082459863 posted

    I'm buidling text search box for a field in a database table. How can I code search all in web form and Stored procedure?

    Monday, December 20, 2010 8:38 AM

Answers

  • User-1181492241 posted

    Hi, Try this.

    ALTER PROCEDURE [dbo].[GetAllPhoneBySearch]
    (    
          @City nvarchar(50) = N''
    )
        
    AS
    BEGIN  
    SET NOCOUNT ON;
    
    SELECT   
            *   
    FROM   
            Phonebook   
    WHERE   
            (Phonebook.City LIKE N'%' + @City + N'%' OR @City IS NULL)  
    
    END 


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 20, 2010 10:30 PM

All replies

  • User269602965 posted

    Show us the SQL query that is using the textbox information to do the database search. 

    Monday, December 20, 2010 11:52 AM
  • User2082459863 posted

    See below SP for text search box:

    ALTER PROCEDURE [dbo].[GetAllPhoneBySearch]

         

          @City nvarchar(50) = N'',

         

         

    AS

          SET NOCOUNT ON;

          SELECT        *

         

          FROM            Phonebook

          WHERE        City LIKE N'%' + @City + N'%'

    Monday, December 20, 2010 8:08 PM
  • User269602965 posted

    So when the Search Box is Empty it is NULL

     

    Try

    WHERE (CITY LIKE N'%' + @CITY + N'%'  OR 1=DECODE(@CITY,NULL,1,0))

     

    ++++++++ Logic

    So when Search Box returns value NULL for parameter @CITY, then 1 = 1,

    and all values are returned 

    And when the Search box returns a value other than NULL for parameter @CITY, then 1 = 0

    and only the filtered CITY LIKE @CITY values are returned.

     

    I did not test, but give it a try.

    Monday, December 20, 2010 9:34 PM
  • User2082459863 posted

    Can you explain DECODE mean? It seems not recognized by T-SQL. Thank you, 

    Monday, December 20, 2010 10:09 PM
  • User-1181492241 posted

    Hi, Try this.

    ALTER PROCEDURE [dbo].[GetAllPhoneBySearch]
    (    
          @City nvarchar(50) = N''
    )
        
    AS
    BEGIN  
    SET NOCOUNT ON;
    
    SELECT   
            *   
    FROM   
            Phonebook   
    WHERE   
            (Phonebook.City LIKE N'%' + @City + N'%' OR @City IS NULL)  
    
    END 


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 20, 2010 10:30 PM
  • User269602965 posted

    DECODE is like IF THEN ELSE built into one COMMAND.

    TSQL may have equivalent SQL command.. it may not.

    DECODE(variable, test variable, True_value, False_value)

    you compare variable to test variable

    if it is true then use the True value

    if it is false then use the False value 

     

    DECODE('France','Germany','F','Not F')

    returns 'Not F' since france is not equal to germany

     

    I guess I get spoiled by using the more fully featured Oracle,

    and in part is why I stopped using other databases years ago.

     

    Read about CASE WHEN ELSE END statement that might be available in TSQL.

     

     

     

     

    Tuesday, December 21, 2010 6:38 PM