Creating stored procedure on IBM DB2

Question

I've created stored procedures before using Microsoft SQL Server Management Studio (SSMS), but doing this for an IBM DB2 database is causing problems.

My SQL for creating the stored procedure is:

CREATE PROCEDURE sp_GetMonthModelCount (in dtStart varchar(6), in dtEnd varchar(6))
    RESULT SETS 1
    LANGUAGE SQL
-- dtStart and dtEnd must be dates of the format YYYYMM
BEGIN
    SELECT DISTINCT DAMDLN, Count(*)
    FROM TEST_DATA.DLRINVAPF
    WHERE DASTAT='RT' AND DADTRS BETWEEN SUBSTR(dtStart, 1, 6) AND SUBSTR(dtEnd, 1, 6);
END

IBM Data Studio looks like this:

As you can see above, when it fails, it tells me INTO clause missing from embedded statement.

Something is different than what I am used to with SSMS. I want to return a table of data. How would I modify my statement?

Answer 1

You can open a cursor from your stored procedure to return the result set of the query by using DECLARE <variable> CURSOR WITH RETURN TO CALLER FOR <statement> and OPEN <variable>.

CREATE PROCEDURE sp_GetMonthModelCount (in dtStart varchar(6), in dtEnd varchar(6))
    RESULT SETS 1
    LANGUAGE SQL
-- dtStart and dtEnd must be dates of the format YYYYMM
BEGIN
    DECLARE c CURSOR WITH RETURN TO CALLER FOR
        SELECT DAMDLN, Count(*) AS month_model_count
        FROM TEST_DATA.DLRINVAPF
        WHERE DASTAT='RT' 
            AND DADTRS BETWEEN SUBSTR(dtStart, 1, 6) AND SUBSTR(dtEnd, 1, 6)
        GROUP BY DAMDLN;
   
    OPEN c;
END

The COUNT in DB2 will require a GROUP BY to run and the DISTINCT can be dropped.