RE: Syntax for SELECT statement Stored Procedure???

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Makes perfect sense.  I took a sample from "Stored Procedures, Triggers and
User Defined Functions on DB2 Universal Database for iSeries" redbook and
modified it to your statement.  I guessed at input data types so you'll have
to modify that piece of it.  Otherwise, I think it should run fine.  You can
test it in iNav's RunSqlScripts (click Start->Run->cwbundbs.exe).

CREATE PROCEDURE MyProc(IN SELUTCSID DECIMAL(3,2), 
                        IN SELUTLCID INTEGER,
                        IN SELUTSVC VARCHAR(10)) 
RESULT SETS 1
LANGUAGE SQL 
BEGIN 
 DECLARE MYCURSOR1 CURSOR WITH RETURN FOR 
   SELECT UTCSID, UTLCID, UTSVC, UTSSTS  
   FROM DODOM1.BFUT220AP 
   WHERE (UTCSID = SELUTCSID AND UTLCID = SELUTLCID AND UTSVC = SELUTSVC);  
  OPEN MYCURSOR1;  
END;

Elvis

-----Original Message-----
Subject: RE: Syntax for SELECT statement Stored Procedure???

Elvis,

But I do want to create SQL Stored Procedures because they are not
dynamically prepared and executed each time they run like in a .ASP or
.NET program.   As I understand it they create "plans"/access paths that
run as static SQL.  

Another reason is what returns should only be one row for the specific
parms/criteria passed.   And, yes, it is possible each of my stored
procedures will be used by the multiple programs in the specific .ASP
application in question AND, in future, by other applications.  

So, for what I want, is the correct syntax?:

CREATE PROCEDURE whatever I want to call it  (anything go between the
parenthesis such as my variable names -- SELUTCSID, SELUTLCID,SELUTSVC,
SELUSSSTS ????) 
LANGUAGE SQL 
  BEGIN 
 DECLARE C1 CURSOR FOR SELECT UTCSID, UTLCID, UTSVC, UTSSTS  FROM
DODOM1.BFUT220AP WHERE (UTCSID = SELUTCSID AND
UTLCID = SELUTLCID AND UTSVC = SELUTSVC);
 OPEN C1;
 RETURN; 
 END 

Where am I missing the boat?

Thanks very much for your help,

Dave