Using Typed Parameter Marker in SQL Procedure 
Author Message
 Using Typed Parameter Marker in SQL Procedure

Hello

I have the following interactive SQL statement that I want to convert
into a procedure :

SELECT SUM(comm) FROM ( SELECT comm FROM staff WHERE comm IS NOT NULL
ORDER BY comm DESC FETCH FIRST  10 ROWS ONLY ) T1 ;

The SP will take an input parameter 'N' to be used in FETCH FIRST 'N'
ROWS portion.  I create the procedure with N as input parameter of type
INTEGER, and TOTAL as the output parameter of type DECIMAL(10,2) which
is the same as "comm" column.

The procedure body is as follows:

BEGIN DECLARE STMT CHAR ( 200 ) ;
SET STMT = ' SELECT SUM ( COMM )
    INTO ?  FROM ( SELECT COMM FROM SAMPLEDB90 . STAFF
    WHERE COMM IS NOT NULL
    ORDER BY COMM DESC
    FETCH FIRST ?  ROWS ONLY ) T1 ' ;

 PREPARE S1 FROM STMT ;
 EXECUTE S1 USING TOTAL , N ;
 END

It can be compiled but returns an error "Token ? was not valid"  when I
call it.

I then look in DB2 SQL Reference manual and find a topic on "Typed
Parameter Marker" that used a form of  CAST(? AS data-type). So, I
replace the first param marker with CAST(? AS DECIMAL(10,2)) and the
second as CAST(? AS INTEGER). I still get an error "CAST was not valid"
when I call it.

Any suggestion on how to make it work ?

I thank you in advance for your help.

Satid S.



Mon, 13 Sep 2004 11:28:40 GMT
 Using Typed Parameter Marker in SQL Procedure

Hi Satid,

According to the SQL Ref allows is only an integer (no parameter marker).

You can e.g. do this:
BEGIN DECLARE STMT CHAR ( 200 ) ;
SET STMT = ' SELECT SUM ( COMM )
    INTO ?  FROM ( SELECT COMM FROM SAMPLEDB90 . STAFF
    WHERE COMM IS NOT NULL
    ORDER BY COMM DESC
    FETCH FIRST ' || CHAR(N) || '  ROWS ONLY ) T1 ' ;

 PREPARE S1 FROM STMT ;
 EXECUTE S1 USING TOTAL;
 END

--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada



Tue, 14 Sep 2004 00:48:20 GMT
 Using Typed Parameter Marker in SQL Procedure
Mr. Serge

I did as U suggested but I still get an error "Token ? was not valid" when I
call it.  Looks like "?" is treated as text string rather than the parameter
marker.

Given the original interactive SQL statement, how would U code it as a
procedure ?  I suspect I may use a wrong approach in my coding.

Thanks
Satid S.

Quote:

> Hi Satid,

> According to the SQL Ref allows is only an integer (no parameter marker).

> You can e.g. do this:
> BEGIN DECLARE STMT CHAR ( 200 ) ;
> SET STMT = ' SELECT SUM ( COMM )
>     INTO ?  FROM ( SELECT COMM FROM SAMPLEDB90 . STAFF
>     WHERE COMM IS NOT NULL
>     ORDER BY COMM DESC
>     FETCH FIRST ' || CHAR(N) || '  ROWS ONLY ) T1 ' ;

>  PREPARE S1 FROM STMT ;
>  EXECUTE S1 USING TOTAL;
>  END

> --
> Serge Rielau
> DB2 UDB SQL Compiler Development
> IBM Software Lab, Canada



Mon, 13 Sep 2004 14:32:35 GMT
 Using Typed Parameter Marker in SQL Procedure
Oops I missed the forest for the trees.

BEGIN DECLARE STMT CHAR ( 200 ) ;

Quote:
> SET STMT = ' SELECT SUM ( COMM )
>     INTO ?  FROM ( SELECT COMM FROM SAMPLEDB90 . STAFF
>     WHERE COMM IS NOT NULL
>     ORDER BY COMM DESC
>     FETCH FIRST ' || CHAR(N) || '  ROWS ONLY ) T1 ' ;

>  PREPARE S1 FROM STMT ;
>  EXECUTE S1 USING TOTAL;
>  END

Your  (next) problem is in the "INTO ?".
Two reasons:
1) SELECT INTO cannot be dynamically prepared
(see SQL Ref)
2) Assuming N is > 1 then SELECT INTO souldn't be the right choice anyways sinc
eit works only for a single row and not for a result set.
3) Which release are you on? FETCH FIRST in subqueries is not supported in V7

Cheers
Serge

PS: Sametime me :-)
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada



Tue, 14 Sep 2004 04:07:58 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Parameter markers using interactive ODBC SQL tool.

2. Using a parameter marker (?) in a View

3. Can Oracle 7 Boolean type be used as out parameter in call a stored procedure

4. Having problems with Parameter Markers in SQL Server 7.0

5. Having problems with Parameter Markers in SQL Server 7.0

6. Using IN predicate with V 7 SQL procedure (or other stored procedure types)

7. Cursor Type Output Parameter for SQL Stored Procedure

8. Using ADODB.Parameters and SQL Text Data type

9. Passing Parameters down to a buried view or stored procedure using parameters

10. Passing multiple parameters for a parameter using temporary stored procedures

11. How to bind BLOB parameter of a stored procedure using Parameter Object of ADO

12. PL/SQL array or record type Parameters for Delphi-4 TStoredProc Parameters


 
Powered by phpBB® Forum Software