RE: SQL stored procedure with list

Charles,

Thank you. I had a feeling it was through a prepared statement

I am having trouble with the S1 and select:

Token 'SELECT was not valid. Valid tokens: : <IDENTIFIER> <PLI_STRIN

CREATE PROCEDURE GetItemNot
(in NotInclude varchar(800))
RESULT SETS 1
LANGUAGE SQL
BEGIN
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;
SET RESULT SETS CURSOR c1;
END


Craig


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Monday, April 12, 2010 12:35 PM
To: Midrange Systems Technical Discussion
Subject: 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,co
st7 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.

--
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.