RE: Syntax for SELECT statement Stored Procedure???

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Dave, you could write a stored procedure that takes 3 input values
(SELUTCSID, SELUTLCID, SELUTSVC) and returns a result set after it runs your
SELECT statement.
I wouldn't do it though, unless that stored procedure is going to be reused
in many different programs.
You simply need to prepare your statement with host variables and then
handle the result set you get keeping in mind you'll get multiple rows back.
This means once your statement runs, you'll need to fetch one row at a time
from the result set and then do something with it.

I've posted some links on similar topic a while back:

http://archive.midrange.com/midrange-l/200603/msg00386.html

BTW, forget about INTO, it has no place in what you're trying to do.

Also, there are some redbook links at this FAQ that might apply to your
situation:

http://www.centerfieldtechnology.com/asktheexpert_viewquestions.asp#Q54

Elvis

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dave Odom
Sent: Monday, April 03, 2006 12:39 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Syntax for SELECT statement Stored Procedure???

I'm trying to create a SELECT SQL Stored Procedure callable from some
.ASP code on a Windoz server.  The .ASP application needs to SELECT
specific rows of data based on parms they put into variables on the CALL
statement.   I have no problem doing INSERT statements from other .ASP
applications, but can't seem to find the correct syntax for doing a
simple SELECT.

According to Operations Navigator's Stored Procedure SQL SELECT
template it indicates the syntax should be:

SELECT COLUMN1, COLUMN8 INTO :variable1, :variable2 from QGPL.TABLE1
WHERE COLUMN1 LIKE 'FILE%';

I entered this:

SELECT UTCSID, UTLCID, UTSVC, UTSSTS INTO SELUTCSID, SELUTLCID,
SELUTSVC, SELUSSSTS FROM DODOM1.BFUT220AP WHERE (UTCSID = SELUTCSID AND
UTLCID = SELUTLCID AND UTSVC = SELUTSVC)

 If I put in the : in front of my parms after the INTO they are
rejected as being bad tokens.  If I take them out and the statement
runs, it returns nothing.   I'm testing this via ISQL from a command
line using a CALL and supplying the parm values since I'm not the
programmer for the .ASP code and don't have any access.

I've also looked in the book DB2 Universal Database for iSeries SQL
Programming Concepts in the section about CREATE PROCEDURE, etc. and
don't find much about doing plain SELECTs

Help would be appreciated from those that know.

Thanks,

Dave Odom