DB2 Stored Procedure - Passing Parameter Issues
in [DB2]
|
Prev: ADM0500E The DB2 Service does not have the necessary authority to complete the command
Next: New Auth Agents on V9.1 FP1 on UNIX
From: wfs on 23 Mar 2010 18:08 Hi All, db2 stored procedure - passing parameters issue static sql within the procedure where char_account_no in ( :ws-acct-list ) if pass 123457 it returns the correct result for the single account if pass '1234567' it returns nothing if pass '1234567','2345678' it returns nothing. Anyone have any idea of the syntax to pass multiple accounts #'s. something like '1234567','2345678','3456789'
From: Mark A on 23 Mar 2010 22:05 "wfs" <wfs1357(a)gmail.com> wrote in message news:5869647e-35be-4f93-ad0b-c84a1c69b352(a)i25g2000yqm.googlegroups.com... > Hi All, > > db2 stored procedure - passing parameters issue > > static sql within the procedure > > where char_account_no in ( :ws-acct-list ) > > > if pass 123457 it returns the correct result for the single account > > if pass '1234567' it returns nothing > > if pass '1234567','2345678' it returns nothing. > > Anyone have any idea of the syntax to pass multiple accounts #'s. > > something like '1234567','2345678','3456789' I am assuming this is a numeric field such as INT, SMALLINT, BIGINT, etc The Standard SQL syntax for that would be where char_account_no in (1234567,2345678,3456789), but you are trying pass them as one parm defined as char or varchar.
From: Mark A on 23 Mar 2010 22:13 "Mark A" <noone(a)nowhere.com> wrote in message news:hobs1c$1u1$1(a)news.eternal-september.org... > I am assuming this is a numeric field such as INT, SMALLINT, BIGINT, etc > > The Standard SQL syntax for that would be > > where char_account_no in (1234567,2345678,3456789), but you are trying > pass them as one parm defined as char or varchar. Correction, I see from the column name (char_account_no) that it is a char column, however, the same problem applies. With static SQL you cannot put multiple columns into one variable, even if you include the commas and quotes. It would work if you used dynamic SQL.
From: wfs on 23 Mar 2010 22:16 On Mar 23, 10:05 pm, "Mark A" <no...(a)nowhere.com> wrote: > "wfs" <wfs1...(a)gmail.com> wrote in message > > news:5869647e-35be-4f93-ad0b-c84a1c69b352(a)i25g2000yqm.googlegroups.com... > > > > > > > Hi All, > > > db2 stored procedure - passing parameters issue > > > static sql within the procedure > > > where char_account_no in ( :ws-acct-list ) > > > if pass 123457 it returns the correct result for the single account > > > if pass '1234567' it returns nothing > > > if pass '1234567','2345678' it returns nothing. > > > Anyone have any idea of the syntax to pass multiple accounts #'s. > > > something like '1234567','2345678','3456789' > > I am assuming this is a numeric field such as INT, SMALLINT, BIGINT, etc > > The Standard SQL syntax for that would be > > where char_account_no in (1234567,2345678,3456789), but you are trying pass > them as one parm defined as char or varchar.- Hide quoted text - > > - Show quoted text - Hi Mark, sorry if I didn't make it clear it's a CHARACTER field - 'char_account_no' in db2 developer workbench (jdbc driver), if I enter a 7 character value without quotes - it finds the data. Putting quotes around it (either single or double) causes no data to be found. Calling it from Dot Net using the odbc / cli driver gives the same result. If I execute the sql directly via SPUFI, using the format; where char_account_no in ( '1234567' , '2345678' , '3456789' ) - works fine - so I presume it must be something to do with a host variable being expected to be a single value.... - sounds like I have to convert it to dynamic sql and do prepare / execute etc The account list can be several thousand long.... so I don't want to code where char_account_no in ( :act01, :act02, :act03 etc.... ) Thanks Bill
From: Mark A on 23 Mar 2010 23:20
> "wfs" <wfs1357(a)gmail.com> wrote in message > news:a85f8629-f1ed-432d-8e9a-5f2b983c9c18(a)z35g2000yqd.googlegroups.com... > - sounds like I have to convert it to dynamic sql and do prepare / > execute etc Yes, I think you need to do dynamic SQL. |