Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Apr 2003
    Posts
    280

    Unanswered: How do you write a query to return all record with parameters?

    For example:

    Select * From Customers Where CustomerName = "Tom" and CustomerLocation = @Location

    I know MS Access you can use a "*" to retrieve all records when there is a parameter. What does SQL Sever query use to retrieve all record?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Do you mean wildcards?

    Access * = SQL Server %

    Check out BOL for wildcards - you don't get quite as many.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2003
    Posts
    280
    The "%" doesn't work....?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by lansing
    The "%" doesn't work....?
    Could you post your Access Q?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2003
    Posts
    280
    Sorry, I'm not using MS Access to do the query. I'm using SQL SERVER 2000 T-SQL to do the query.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by lansing
    Sorry, I'm not using MS Access to do the query. I'm using SQL SERVER 2000 T-SQL to do the query.
    Lol.

    Yes - that is why you and I are posting in the SQL Server forum. However all you have given me is something about using * to get all records using parameters. I suspect you mean wildcards and all I get back is "it doesn't work".

    If you can show me some code of some sort (for example, a working Access query) that illustrates what you want we can probably sort it.

    BTW - by records you do mean records right? Not fields...records?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2003
    Posts
    280
    This example should be good enough.
    For example:

    Select * From Customers Where CustomerName = "Tom" and CustomerLocation = @Location

    RECORDS as in all the rows in the table that the CustomerName is equal to TOM and all the places in the CustomerLocation.

    CustomerLocation:
    FL
    LA
    AL
    WA

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    So - @Location would be * in Access?

    bear with me - I think I know exactly where we are headed
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2003
    Posts
    280
    Well, just think of @Location is the Paramter. In access, it use the paramter as [Forms]![TextBox].Value for a combox box.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    lol.

    Blimey.

    Ok - I will fill in the gaps.

    I presume then that you mean the value of @Location (and yes - it could be a combo or something in Access) would be * in access and you have tried % in SQL Server?

    The problem is not with the Wildcards (again please check in BOL). Your query would not work in Access either. In order to use Wildcasrds you cannot use =. You have been searching for fields with the character "%" in. You need to swap the = for LIKE.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Apr 2003
    Posts
    280
    How do you use the LIKE with the parameter in the T-SQL?
    I have try this:
    Like @Location but T-SQL doesn't let me.

  12. #12
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    For goodness sake lansing ... this is sql 101

    Select * From Customers Where CustomerName = "Tom" and CustomerLocation = @Location
    could be

    Select * From Customers Where CustomerName = "Tom"

    or

    Select * From Customers Where CustomerName = "Tom" and CustomerLocation is not null

    or

    create procedure usp_Find_Tom @location varchar(50) = null
    AS
    if @location is not null
    BEGIN
    Select * From Customers Where CustomerName = "Tom" and CustomerLocation = @Location
    ELSE
    Select * From Customers Where CustomerName = "Tom"
    END

    or any other number of derivatives


    This is not rocket surgery

    -- This is all just a Figment of my Imagination --

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Gotta second that attitude. "How do you use the LIKE with the parameter in the T-SQL?" Did you TRY to look it up in Books Online? First, read the manual, then we'd be glad to help with clarifications and usage.
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Apr 2003
    Posts
    280
    Ok, here is the deal. I forgot to mention that I'm using the combo box to retrieve my data at for the parameter.

    Example combo box item:
    ALL
    1
    2
    3
    4
    5

    tom53h: Grow up!

    blindman: I have read most of the article online already to do T-SQL beside I had made myself an T-SQL query. It seem the query doesn't like the word LIKE with the value in the where clause (Select * From Customer Where CustomerID Like 1). But it does work for the % (Select * From Customer Where CustomerID Like '%').

    What else can I do? btw, I'm using Visual Basic.Net to retrieve the data.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by lansing
    tom53h: Grow up!
    Lol.

    Ok - run this in Query Analyser.

    What is different to your query?

    Code:
    If EXISTS(SELECT * FROM sysobjects O where O.name = 'ParamT' AND O.xtype = 'U') BEGIN
        DROP TABLE ParamT
    End
     
    CREATE TABLE ParamT (MyCol Char(10))
     
    INSERT INTO ParamT
    SELECT '1' UNION
    SELECT '2' UNION
    SELECT '3' UNION
    SELECT '4'
     
    DECLARE @Param AS CHAR(10)
     
    SELECT @Param = '1'
     
    SELECT *, @Param AS TheParam FROM ParamT
    WHERE MyCOl LIKE @Param
     
    SELECT @Param = '%'
     
    SELECT *, @Param AS TheParam FROM ParamT
    WHERE MyCOl LIKE @Param
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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