Re: SQL stored procedure with list

The same way he's doing it in MS SQL server, by using a dynamic
statement instead of the static statement you currently have.

declare C1 cursor
with hold
with return to client
for S1;

prepare S1 from 'SELECT
imdsc,impn,pscmrn,imlots,psqtyn,cost1,cost2,cost3,cost4,cost5,cost6,cost7
FROM PNOTEBOOK.NBKSUM WHERE impn NOT IN ' + '(' + NotInclude + ')';

open C1;

Note that if NotInclude's value is from user input, then you will have
a huge security hold in either MS SQL Server or DB2 on the i. Google
"SQL Injection" for more info.

HTH,
Charles

On Mon, Apr 12, 2010 at 12:03 PM, hotmail <craigjacobsen@xxxxxxxxxxx> wrote:

I am trying to create an SQL stored procedure for a .net programmer that may
pass a list in one parameter.
It works if I only pass in 1 item part number.  The .net programmer said he
had it working in sequel server and he could pass item1,item2,etc.
Here is what he gave me from the sequel server:

CREATE PROCEDURE some_name

@productNumberString  VARCHAR(750)='''000000'''

AS

           DECLARE @select        VARCHAR(150)

           DECLARE @where        VARCHAR(800)

           SET @select = 'SELECT
imdsc,impn,pscmrn,imlots,psqtyn,cost1,cost2,cost3,cost4,cost5,cost6,cost7
FROM PNOTEBOOK.NBKSUM WHERE impn NOT IN '

           SET @where = '(' + @productNumberString + ')'

           EXEC(@select+@where)

GO

This is what I created.  I can't figure out how to pass this as one string
for a DB2 stored proc.

CREATE PROCEDURE GetItemNot
(in NotInclude varchar(800))
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR FOR   SELECT
imdsc,impn,pscmrn,imlots,psqtyn,cost1,cost2,cost3,cost4,cost5,cost6,
cost7
 FROM PNOTEBOOK/NBKSUM
  WHERE impn NOT IN (NotInclude);
OPEN c1;
SET RESULT SETS CURSOR c1;
END

Thank you,

Craig
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.