Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    7

    Unanswered: Multiple parameters to a stored procedure

    Hi All,

    I have a database with very heavy volume of data.

    I need to write a stored procedure with 20 parameters as input and it searches in a table . Most of the parameters or NULL , how do I write this procedure without using any dynamic queries.

    Ex : To find a customer I have a proc which can accept 20 parameters like CustName, City, State , Phone , Street etc.

    Im passing only Custname as parameters and other 19 parameters are NULL.How do I write the WHERE clause ?

    Thanks in advance,

    HHA

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Welcome to the forum

    A common solution for this is some thing like:
    Code:
    .......
    WHERE (Field1 = @Param1 OR @Param1 IS NULL)
    AND (Field2 = @Param2 OR @Param2 IS NULL).....
    HHA -
    Hope Helps Acoming?
    Homeboys Help Ame?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2006
    Posts
    7
    Thanks !!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    But be aware that this is one of the cases where dynamic sql is preferable. A dynamically executed query containing only one or two valid comparisons will run much faster than a non-dynamic query containing 18 superfluous 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
    Jun 2005
    Posts
    319
    Quote Originally Posted by blindman
    But be aware that this is one of the cases where dynamic sql is preferable. A dynamically executed query containing only one or two valid comparisons will run much faster than a non-dynamic query containing 18 superfluous criteria.
    What he said. We use dynamic SQL all the time for these types of requirements.

Posting Permissions

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