Hello,
I have created a stored procedure that receives several parameters. I'm
having problems with one of the parameters. The parameter that I'm
having problems with is a parameter that I want to use as part of an IN
expression. My sp looks like this...
CREATE PROCEDURE COHAMIRA.SP_EQTOT (
IN I_SITEID DECIMAL(3, 0) ,
IN I_FROMDATE DECIMAL(7, 0) ,
IN I_TODATE DECIMAL(7, 0) ,
IN I_EQUIPSTSCODE CHAR(3) ,
IN I_INEXPRESSION CHAR(130) )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC COHAMIRA.SP_EQTOT
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN
DECLARE C1 CURSOR FOR
SELECT CEITM , ITDSC1 , COUNT ( * ) COUNT FROM OHILIVFILE . CONVRPF
INNER JOIN
OHILIVFILE . ITMSTPF ON CENROV = ITNROV AND CEITM = ITPTNO
WHERE CENROV = I_SITEID AND CEITM IN I_INEXPRESSION AND
CESDT BETWEEN I_FROMDATE AND I_TODATE AND CESTC = I_EQUIPSTSCODE
GROUP BY CEITM , ITDSC1 ;
OPEN C1 ;
END ;
The problem I'm having is with the I_INEXPRESSION parameter. I'm
calling this procedure from a .net program. Before I call this
procedure, I build the parameter to look like that way it should, like
so:
('VALUE1','VALUE2','VALUE3')
For some reason, it doesn't like this and the sp returns an empty data
set. When I run it with just one value (that I hard-code), it runs
fine. I pass the value as a string, like so:
"AB2100" for example. I get a data set back. The problem comes up when
I attempt to use several values. How do I build this parameter so that
it effectively works correct in the IN expression?
Any help would be greatly appreciated...
Thank you,
Antonio Mira
Application Developer - Mid-Ohio Division
Time Warner Cable
1015 Olentangy River Road - 2nd Floor
Columbus, OH 43212
http://www.timewarnercable.com
phone: 614 827 7949
This E-mail and any of its attachments may contain Time Warner
Cable proprietary information, which is privileged, confidential,
or subject to copyright belonging to Time Warner Cable. This E-mail
is intended solely for the use of the individual or entity to which
it is addressed. If you are not the intended recipient of this
E-mail, you are hereby notified that any dissemination,
distribution, copying, or action taken in relation to the contents
of and attachments to this E-mail is strictly prohibited and may be
unlawful. If you have received this E-mail in error, please notify
the sender immediately and permanently delete the original and any
copy of this E-mail and any printout.
midrange.com
