Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    5

    Question Unanswered: Bind variables and WHERE with CASE

    Hi,

    we've the following construct under SqlServer2005/Oracle 10g.
    paramID is a input param of type int.

    Code:
    SELECT p.ID, p.Field1, p.Field2
    FROM    PLANNING p
    WHERE  (CASE inputParamID WHEN NULL THEN inputParamID ELSE p.ID END )= inputParamID;
    Now i have to build a version with BindVariables:
    Code:
    statement :=
    SELECT p.ID, p.Field1, p.Field2
    FROM    PLANNING p
    WHERE  (CASE :pID WHEN NULL THEN  :pID ELSE p.ID END )=  :pID;
    
    OPEN resultCursor FOR statement
    USING  paramID;
    If i take this code/block into the query/editor window of Toad, i get a dialog where i put in the value for : pID and i get my result data correct.

    If i put the block into the stored procedure it has to be, i get the message
    "1008 --- ORA-01008: not all variables bound".
    The value for : pID is the same as in the editor window.

    Can somebody help me or explain why i get this currious error?

    Thx a lot.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Bad parameter

    You need to "bind" all parameters:
    Code:
    SELECT p.ID, p.Field1, p.Field2
    FROM    PLANNING p
    WHERE  (CASE :1 WHEN NULL THEN  :1 ELSE p.ID END )=  :1;
    
    OPEN resultCursor FOR statement
    USING  paramID;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2009
    Posts
    5
    Hi,

    sry but i don't understand what you mean, please explain it to me.

    Is there a difference betwenn the normal execute and the sp?
    Why rolled eyes?

  4. #4
    Join Date
    Apr 2009
    Posts
    5
    Sry, i've now understand it.

Posting Permissions

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