best way to parameterize an "in (...)" part of query?

From: <mh_at_pixar.com>
Date: Sun, 01 Mar 2009 06:50:59 GMT
Message-ID: <nfqql.7924$jZ1.6948_at_flpi144.ffdc.sbc.com>

I have two relatively large procedures that differ only by the contents of an "in (...)" clause, e.g.

    create proc1 as
...

       select foo from bar where x in ('a','b')
...

    create proc2 as
...

       select foo from bar where x in ('x','y')
...

I would like to parameterize this, so that proc1 and proc2 can call a common subroutine, and pass in the "in" list.

    create proc1 as ... baseproc(['a','b']) -- some representation of a,b     create proc2 as ... baseproc(['x','y']) -- some representation of x,y

    create baseproc(INLIST SOMETYPE)
...

       select foo from bar where x in INLIST
...

How can I parameterize this? Is there an appropriate type?

Many TIA!!
Mark

-- 
Mark Harrison
Pixar Animation Studios

Received on Sun Mar 01 2009 - 00:50:59 CST