Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2004
    Posts
    15

    Unanswered: Need help writing search query

    I am not very familiar with the syntax of MS SQL and I am trying to write a stored procedure which would do a search and return matching records.
    This is what I need to achieve:
    for instance I create a form with 4 text fields
    - First Name
    - Last Name
    - Employee ID
    - Date

    I am interested in writing a stored procedure that would run a select query based on the input in the text fields
    e.g.
    - if the user enters First Name and the Last Name (leaving Employee ID and Date fields blank) query should be something like
    Code:
    select * from Employee where FirstName like @FirstName and LastName like @LastName
    - or if the user enters only the Employee ID stored procedure should run a query similar to
    Code:
    select * from Employee where EmployeeID like @EmployeeID

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    select	*
    from	Employee
    where	(FirstName like @FirstName and LastName like @LastName)
    	or
    	(EmployeeID like @EmployeeID)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2004
    Posts
    15

    Thanks for the prompt response

    Thanks for the your time blindman .. that was absolutely brilliant, shows you know your SQL
    This query would sure work for the example I posted, but I was just wondering if I can give user more flexibility and allow him to enter lets say [partial first name (some string with wildcard characters) and partial ID] or [partial first name, last name and ID] or some such wierd combination. The query should adapt to the input and return result accordingly.
    What is the best way to go about doing this, again thanks for any help I can get.
    Thank you.
    Have a great day.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes.

    You will need to add more complexity to your WHERE clause to accomplish the logic you want.

    You will also need to use the LIKE operator if you want to allow wildcards.

    You will also need to expect this query not to run very fast, if you include lots of logical operators and LIKE comparisons in your criteria....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2005
    Posts
    78
    Since this is in a stored procedure you might use dynamic SQL to create the query on the fly and then execute it.

    eg.

    create x @empid int, ...

    declare @sql varchar(200)

    set @sql = 'select * from Employee where '

    if empid is not null set @sql = @sql & 'EmployeeID =' & @empid

    ... etc etc

    exec (@sql)

    ...

  6. #6
    Join Date
    Jul 2004
    Posts
    15
    Thanks ejustuss and blindman, you guys were a tremendous help.
    Since I am not very comfortable writing stored procedures this was my solution to the problem .. nothing ingenious but hey as long it works thats all I care about .
    So I wrote this insanely strict stored procedure which would except user to input all the parameters (first name, last name, Employee ID, Date .... everything) and wrote a query something similar to this:
    Code:
    select * from Employee where FirstName like @FirstName and LastName like @LastName and EmployeeID like @EmployeeID and .....
    Since my stored procedure is not giving any flexibility to the user, I added flexibility on the client side in the web form code by replacing all fields left blank by the user with wildcard '%'. So if a user wanted to search by employee's first name, he would just enter the first name leaving other field blanks. These blank fields will be replaced with % and passed to the stored procedure.

  7. #7
    Join Date
    Oct 2003
    Posts
    84
    Not sure how effecient this is :

    SELECT * FROM Employee
    WHERE EmployeeID LIKE ISNULL('%' + @EmployeeID + '%', EmployeeID)
    AND FirstName LIKE ISNULL('%' + @FirstName + '%', FirstName)
    AND LAstName LIKE ISNULL('%' + @LastName + '%', LastName)
    AND DATEDIFF(Day, ISNULL(@Date, Date), Date) = 0;

  8. #8
    Join Date
    Jul 2004
    Posts
    15
    Hey afx thanks for posting your version of the solution. I am sorry but I really did not understand the code you posted. Though your input was definetely useful since using ISNULL is by far a way better more efficient option
    This is how I would use ISNULL

    SELECT * FROM Employee
    WHERE EmployeeID LIKE ISNULL( @EmployeeID, '%')
    AND FirstName LIKE ISNULL( @FirstName, '%')
    AND LAstName LIKE ISNULL( @LastName, '%')


    Thank you again for the input and effort

  9. #9
    Join Date
    Feb 2005
    Posts
    1

    SQL query help

    Hi all,

    I need help on building query statement. below is my table called inventory.

    idx fabidx coloridx qty isReserved
    1 1 1 15 Y
    2 1 2 20
    3 1 1 10
    4 1 1 25 Y
    5 1 2 23 Y
    6 1 3 26
    This is the output that i'm expecting.
    fabidx coloridx qty isReserved
    1 1 50 2
    1 2 43 1
    1 3 26 0
    I need to get all distinct fabidx and coloridx, i need to get the sum of the distinct fabidx and coloridx, and i need to get the count of "Y" in the isReserved column of the distinct fabidx and coloridx.

    Please help. Thanks.
    Last edited by xilly7; 02-23-05 at 18:02.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A simple aggregation query. Look up aggregation function in Books Online, and then post this as a new thread if you still need help.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.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
  •