Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    9

    Unanswered: Passing IN() values as parameter

    Hi...

    I'm creating a procedure to fetch rows from table. One field will come come as IN(). Its the condition. That field is numeric field (note down), i would like to pass the In values as parameter.

    eg: procedurename @fieldvalue varchar(100)
    as
    begin
    ...

    WHERE fieldname IN(@fieldvalue)


    while executing the procedure how to pass the value... or procedure itself has problem...?
    Help me...

    Tx in Advance...

  2. #2
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74
    Once again dynamic SQL seems to be the deal here, unless a more complete description of the problem is available.
    The thing here is that you cannot do a procedure that works in the way you describe without using dynamic SQL, because of the datatype clash.

    Your IN clause wants to do comparisons with integers, and therefore your argument to the procedure will never be able to work unless you convert your values in the fieldvalue variable to a string which you add to the the last part of your existing query, and execute it with dynamic SQL. I cannot judge whether this is the right decision to do in your particular case without more information about the problem. Hope I got your thinking going atleast ...
    The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In your procedure, parse the parameter into a temp table. Join your temp table to the production table to limit the result set. Works every time, and often runs much faster than the dynamic SQL solution.

    -PatP

Posting Permissions

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