Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2005
    Posts
    76

    Unanswered: parameters for query

    I'm not sure if any1 can answer is on this forum, but any help would be VERY appriceted....

    I am creating a report using Visual Studio.Net..I want to write a query using parameters BUT in this case I dont no what the parameter will be...

    ie the user can enter a customer account OR a customer group
    and sort can be on product OR total sales...

    Any ideas?!

    Thanks!!!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The intuitively obvious answer would be to use a stored procedure instead of a query, then build the "smarts" into the procedure. There may be better answers, but that one jumps out at me.

    -PatP

  3. #3
    Join Date
    Feb 2005
    Posts
    76
    Quote Originally Posted by Pat Phelan
    The intuitively obvious answer would be to use a stored procedure instead of a query, then build the "smarts" into the procedure. There may be better answers, but that one jumps out at me.

    -PatP
    coool... neva dun that wiv stored proc... how would i do that??? or do u know any useful links?!

    THANKS

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sounds like dynamic sql to me
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    Sounds like dynamic sql to me
    That is the fools way out... Infinite flexibilty, infinite risk.

    I was hoping to kind of leash things a bit, finding a good compromise between flexibility and safety.

    -PatP

  6. #6
    Join Date
    Feb 2005
    Posts
    76
    So would the best idea be to write a stored proc somefing like this?!:

    CREATE PROCEDURE test (@parameter varchar, @variable varchar) AS
    SELECT * from SALES
    where @parameter = @variable
    GO

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    CREATE PROCEDURE test (@parameter1 varchar, @parameter2 varchar) AS
    SELECT * from SALES
    where (Column1 = @Parameter1 or @Parameter1 is null)
          and (Column2 = @Parameter2 or @Parameter2 is null)
    ...but dump the "select *", and let your interface handle the sorting.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Feb 2005
    Posts
    76

    Red face

    Quote Originally Posted by blindman
    Code:
    CREATE PROCEDURE test (@parameter1 varchar, @parameter2 varchar) AS
    SELECT * from SALES
    where (Column1 = @Parameter1 or @Parameter1 is null)
          and (Column2 = @Parameter2 or @Parameter2 is null)
    ...but dump the "select *", and let your interface handle the sorting.

    That cant be write.... if they enter a value for parameter 1 (eg 'X' only then the query will become:

    where (Column1 = 'X')
    and (Column2 is null)

    right?! That would not be write...it would return nothing.....

    I dun no how 2 right it so the user can enter a account no OR customer name....

  9. #9
    Join Date
    Feb 2005
    Posts
    76
    would this work:

    if @Account Is Not Null then
    write query using @Acount in where part
    break
    else if @Group Is Not Null then
    write query using @Group in where part

    break
    end

    ????

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, but you should set defaults for the parameters, like so:
    Code:
    CREATE PROCEDURE test (@parameter1 varchar = null, @parameter2 varchar = null) AS
    SELECT * from SALES
    where (Column1 = @Parameter1 or @Parameter1 is null)
          and (Column2 = @Parameter2 or @Parameter2 is null)
    If a parameter is submitted, the result set will be filtered on that value. If the parameter is ommitted, no filtering will occur on the column.
    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
  •