locked
Need help on Search query. RRS feed

  • Question

  •  

    Hello All!

     

    I'm building a serch page on  website. It basicly searches 2  tables.  Right now, the way I have it so the user can enter a value in one of the fields, and not the others.  And it will return the value. For example, put in '34' in the age field, it will return all people with 34. Now if you add 'Jones' to the last name field, it will return all people with the age of 34, and all people with Jones.

     

    But what if I want it so it returns only the people with the age 34, and Jones. But leave the other fields blank. The later than add 'Jim' to the first name, so now it will return 34, jones, jim.  I tried to use the AND operator, but then it requires that field to be filled.  Here is my code.

     

    ALTER PROCEDURE [dbo].[SrchActiveII] 

     @FName nvarchar(50)=NULL,
     @LName nvarchar(50)=NULL,
     @DOB nvarchar(50)=NULL,
     @Acct nvarchar(50)=NULL,
     @Login nvarchar(50)=NULL,
     @Status nvarchar(50)=NULL,
     @Rmark nvarchar(255)=NULL,
     @Room nvarchar(50)=NULL,
     @Age nvarchar(50)=NULL,
     @Type nvarchar(50)=NULL,
     @Misc bit = NULL
    AS
    BEGIN
     SELECT     Active_Orders.First_Name,  Active_Orders.Last_Name, Active_Orders.Account_Number, Order_Status.Status, Active_Orders.Remarks,
                          Locations.Loct_Desc, Active_Orders.Rm_Desc, Active_Orders.Age,  Active_Orders.Type, Active_Orders.Stat,
                          Active_Orders.Order_ID, Active_Orders.Login, Active_Orders.Misc
    FROM         Active_Orders INNER JOIN
                          Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN
                          Locations ON Active_Orders.Location_ID = Locations.Location_ID
    WHERE (Active_Orders.First_Name =@FName) OR (First_Name IS NULL) OR
                          (Active_Orders.Last_Name =@Lname) OR (Last_Name IS NULL) OR
                          (Active_Orders.DOB =  @DOB) OR (DOB IS NULL) OR
                          (Active_Orders.Account_Number =  @Acct) OR (Account_Number IS NULL) OR
                          (Order_Status.Status =  @Status) OR (Status IS NULL) OR
                          (Active_Orders.Remarks = @Rmark) OR (Remarks IS NULL) OR
                          (Active_Orders.Rm_Desc = @Room) OR (Rm_Desc IS NULL) OR
                          (Active_Orders.Age = @Age) OR (Age IS NULL) OR
                            (Active_Orders.Type =  @Type) OR ([Type] IS NULL) OR
          (Active_Orders.Login =  @Login) OR ([Login] IS NULL) OR
          (Active_Orders.Misc = @Misc) OR (Misc IS NULL)
    END

    Thanks!

     

    Rudy

    Thursday, September 6, 2007 1:02 PM

All replies

  • Dynamic Search Conditions in T-SQL

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

     

     

    AMB

    Thursday, September 6, 2007 1:30 PM
  •  

    Try writing the query this way:

     

    Code Snippet
    select
    * from tablename where ColumnName=isnull(@paramname,ColumnName)

     

     

    Here if the value of @paramname is null then the condition will be satisfies and all the rows will be selected. and @paramname has got value then rows will be selected based on @paramname

    Thursday, September 6, 2007 1:31 PM
  • That is not a good advice. If [tablename] has an index by [ColumnName], the query optimizer will not try to implement an "index seek" operation, even if the cardinality of the parameter is high. Check the execution plan of these two stored procedures.

     

    Code Snippet

    use northwind

    go

     

    create procedure dbo.usp_p1

    @orderid int

    as

    set nocount on

    select *

    from dbo.orders

    where orderid = isnull(@orderid, orderid)

    go

     

    dbcc freeproccache

    go

    exec dbo.usp_p1 null

    go

    dbcc freeproccache

    go

    exec dbo.usp_p1 10250

    go

     

    alter procedure dbo.usp_p1

    @orderid int

    as

    set nocount on

    select *

    from dbo.orders

    where orderid between coalesce(@orderid, 0) and coalesce(@orderid, convert(int, 0x7FFFFFFF))

    go

     

    dbcc freeproccache

    go

    exec dbo.usp_p1 null

    go

    dbcc freeproccache

    go

    exec dbo.usp_p1 10250

    go

     

    drop procedure dbo.usp_p1

    go

     

     

     

     

    AMB

    Thursday, September 6, 2007 2:10 PM
  •  

    Thanks AMB for the information.
    Thursday, September 6, 2007 2:25 PM
  • Hi AMB!

     

    Will either of the SP will work? I'm not sure what "dbcc" is doing. I'm a little confused.

     

    Thanks!

     

    Rudy

    Friday, September 7, 2007 3:49 PM
  • Hello AMB!

     

    I been reading through the doc you sent. Thank you for that.  But it seems like it would only return certain values.  I would still need to return all the values in my SELECT statement.

    Yout thoughts?

     

    Rudy

     

     

    Friday, September 7, 2007 4:30 PM
  • Hi all!

     

    I'm getting closer. Based on the great information here this is what I got to work, but.....

    It's not exactly how I need it. So have several search critera, I have a first name like 'Joe', it will bring back all the Joes.  If I have a last name like 'Jones', it will return all the Joes, and all the Jones.

     

    How can I set it up so if one 'Joe' is selected, it will bring back all the Joes, but if ' Jones' is selected, it will now only bring back all the 'Joe Jones', and nothing else. It's seems like a diffrence between ans 'AND', 'OR' opereator.  But How can I make that dynamic.

     

    THE CODE:

    @FName nvarchar(50)=NULL,

    @LName nvarchar(50)=NULL,

    @DOB nvarchar(50)=NULL,

    @Acct nvarchar(50)=NULL,

    @Login nvarchar(50)=NULL,

    @Status nvarchar(50)=NULL,

    @Rmark nvarchar(255)=NULL,

    @Room nvarchar(50)=NULL,

    @Age nvarchar(50)=NULL,

    @Type nvarchar(50)=NULL,

    @Misc bit = NULL

    AS

    DECLARE @sql nvarchar(4000),

    @paramlist nvarchar(4000)

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    --SET NOCOUNT ON;

    SELECT @sql =

    'SELECT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Order_Status.Status, Active_Orders.Remarks,

    Locations.Loct_Desc, Active_Orders.Rm_Desc, Active_Orders.Age, Active_Orders.Type, Active_Orders.Stat,

    Active_Orders.Order_ID, Active_Orders.Login

    FROM Active_Orders INNER JOIN

    Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN

    Locations ON Active_Orders.Location_ID = Locations.Location_ID

    WHERE 1 = 1';

    SET @paramlist = N'@Fname nvarchar(50), @Lname nvarchar(50), @DOB nvarchar(50),

    @Acct nvarchar(50), @Login nvarchar(50), @Status nvarchar(50),

    @Rmark nvarchar(50), @Room nvarchar(50), @Age nvarchar(50),

    @Type nvarchar(50), @Misc bit'

    If @FName IS NOT NULL

    SET @SQL = @SQL + N'And Active_Orders.First_Name LIKE @Fname + "%"'

    If @LName IS NOT NULL

    SET @SQL = @SQL + N'And Active_Orders.Last_Name LIKE @Lname + "%"'

    If @DOB IS NOT NULL

    SET @SQL = @SQL + N'And Active_Orders.DOB LIKE @DOB + "%"'

    If @Acct IS NOT NULL

    SET @SQL = @SQL + N'And Active_Orders.Account_Number LIKE @Acct + "%"'

    If @Login IS NOT NULL

    SET @SQL = @SQL + N'And Active_Orders.Login LIKE @Login + "%"'

    If @Status IS NOT NULL

    SET @SQL = @SQL + N'And Active_Orders.Status LIKE @Status + "%"'

    If @Rmark IS NOT NULL

    SET @SQL = @SQL + N'And Active_Orders.Remarks LIKE @Rmark + "%"'

    If @Room IS NOT NULL

    SET @SQL = @SQL + N'And Active_Orders.Rm_Desc LIKE @Room + "%"'

    If @Age IS NOT NULL

    SET @SQL = @SQL + N'And Active_Orders.Age LIKE @Age + "%"'

    If @Type IS NOT NULL

    SET @SQL = @SQL + N'And Active_Orders.Type LIKE @Type + "%"'

    If @Misc IS NOT NULL

    SET @SQL = @SQL + N'And Active_Orders.Misc LIKE @Misc + "%"'

     

     

    END

     

    Thanks!

     

    Rudy

    Tuesday, September 11, 2007 7:21 PM