Passing schema name as parameter in db2 - unix

Question

I have one stored proc in db2 (luw) on schema A which looks like below.

CREATE PROCEDURE A.GET_TOTAL (IN ID CHARACTER(23))
    BEGIN
        DECLARE CURSOR1 CURSOR WITH HOLD WITH RETURN TO CLIENT FOR  
        SELECT * FROM B.employee e where e.id=ID
    END

Given sproc which exist on schema "A" runs query on another schema "B". This another schema name B may changed based on application logic. How can i pass the schema name as parameter to this sproc?

Answer 1

First, I do not think that stored procedure works because the select statement is not defined in a cursor or a prepared statement.

If you want to execute a dynamic SQL in a stored procedure, you need to define in a stmt, then prepare it and execute it.

Let's suppose you pass the schema name as parameter; If you want to change the schema, you can execute dynamically "set current schema" or concatenate the schema name in your query.

For more information: http://www.toadworld.com/platforms/ibmdb2/w/wiki/7461.prepare-execute-and-parameter-markers.aspx