[转帖]如何在PowerBuilder中调用MS SQL SERVER的存储过程

ehxz

免费乐捐，感谢支持

 

Overview The examples in this document are intended to extend the information provided with our Online Documentation and manuals. How do I get the output parameters back from the server if my stored procedure is also returning a resultset? There are a couple of different ways to get the RESULTSET and OUTPUT parameter from a stored procedure. Code Sample 1: DECLARE, EXECUTE and FETCH ResultSet and Output parameters: The first is to declare, execute and fetch the resultset and then when the sqlcode = 100 get out of loop and fetch the procedure a second time.  The code would look like this: //declare local variable LONG l_parm1 LONG l_out_parm STRING s_message CONNECT USING SQLCA; //Initialize the input parameter - this could be hard coded. l_parm1 = 35 DECLARE testproc PROCEDURE FOR dbo.testproc @Parm1 = :l_parm1, @OutParm = :l_out_parm OUTPUT USING SQLCA; EXECUTE testproc; //First, fetch the RESULTSET do while sqlca.sqlcode = 0 FETCH testproc INTO :s_message; if sqlca.sqlcode = 0 then MessageBox( "s_message", s_message) end if loop //Now fetch the OUTPUT PARM FETCH testproc INTO :l_out_parm; MessageBox( "l_out_parm", String(l_out_parm)) CLOSE testproc; DISCONNECT USING SQLCA; The second way this can be accomplished is by using the RPC method.  This is discussed in the "Application Techniques" manual. Code sample 2:   Dynamic SQL Format 4 declaring a stored procedure The sample in our help file illustrates ways to use format 4 using a declared cursor.  This script uses Format 4 embedded SQL statements and a declared stored procedure. This example assumes you know that there will be only one output descriptor and that it will be an integer. You can expand this example to support any number of output descriptors and any data type by wrapping the CHOOSE CASE statement in a loop and expanding the CASE statements. string ls_procname, ls_sql, ls_Temp int li_job_id, li_Ctr, li_Temp int li_rtn li_job_id = dw_emp.getitemNumber(1, "job_id") setNull(li_job_id) ls_procname = 'pr_405237' ls_sql = 'execute ' + ls_procname + ' @job_id=' + '?' PREPARE SQLSA FROM :ls_sql using sqlca; DESCRIBE SQLSA INTO SQLDA ; DECLARE my_procudure DYNAMIC PROCEDURE FOR SQLSA ; li_rtn = SQLDA.SetDynamicParm(1, li_job_id) sle_1.Text = String(li_rtn) EXECUTE DYNAMIC my_procudure USING DESCRIPTOR SQLDA ; FETCH my_procudure USING DESCRIPTOR SQLDA ; If Sqlca.Sqlcode <> 0 then     Messagebox("Error ", String(Sqlca.Sqlcode) + sqlca.sqlerrtext)  else        for li_Ctr = 1 to sqlda.NumOutputs           CHOOSE CASE SQLDA.OutParmType[li_Ctr]             CASE TypeString!               ls_Temp = GetDynamicString(SQLDA, li_Ctr)             CASE TypeInteger!               li_Temp = GetDynamicNumber(SQLDA, li_Ctr)           END CHOOSE        next   end if CLOSE my_procedure ;