Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012
    Posts
    15

    Unanswered: Issue with Fetching count into local variable

    Hi

    I am trying to fetch count of records from a table and assign it to a local variable. The table is created in the stored procedure hence I am forced to use dynamic SQL.

    SELECT COUNT(1) FROM PFE.PS_PFE_DLY_F_Z1

    Tried the below approach but cant get the EXECUTE to run a prepared Select query (not sure why Select is not supported in EXECUTE)

    SET v_DynSQL='SELECT COUNT(1) FROM PFE.PS_PFE_DLY_F_Z1';

    PREPARE v_PrepSQL from v_DynSQL;

    EXECUTE v_PrepSQL into v_row_count;

    Below is the error:

    SQL0518N The statement named in the EXECUTE statement is not in a prepared
    state or is a SELECT or VALUES statement. SQLSTATE=07003


    EXECUTE IMMEDIATE has an option of BULK INTO using arrays but since I know that only one row will be fetched is there a Simple way of handling the above ?

    Thanks

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    create procedure ttt ( out cnt integer)
    language sql
    begin
    DECLARE v_row_count integer ;
    DECLARE v_DynSQL varchar(125) ;
    DECLARE C1 cursor for v_PrepSQL ;
    SET v_DynSQL='SELECT COUNT(*) FROM DBA.GRANT';
    PREPARE v_PrepSQL from v_DynSQL;
    open c1 ;
    fetch c1 into CNT ;
    end

    (4)[db2inst1@dlx00031 work]$ db2 "call db2inst1.ttt(?)"

    Value of output parameters
    --------------------------
    Parameter Name : CNT
    Parameter Value : 5211

    Return Status = 0
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Dec 2012
    Posts
    15
    Thanks for the reply, now I have a similiar issue where i want to include a 'WHERE' clause with parameter marker and fetch the count, Wondering how to prepare a dynamic sql statement with parameter markers and associate a cursor for it and fetch the result into a variable.

    Please help

  4. #4
    Join Date
    Dec 2012
    Posts
    15
    It looks like the OPEN Cursor has Using clause.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •