Stored procedure parameter

If the parameter name or number does not match, PowerBuilder
will display an error indicating that the parameter does not exist;
while PowerServer will check the schema of stored procedure or
function and automatically match the corresponding parameter type and
position, therefore PowerServer may be able to execute the stored
procedure without errors.

Suppose the stored procedure is like this:

CREATE  PROCEDURE [dbo].[up_trs_003]
  @v_out_name varchar(20) Output 
AS
  Select @v_out_name = v_name   From trs_emp where c_id = '101'

CREATE PROCEDURE [dbo].[up_trs_003]

@v_out_name varchar(20) Output

Select @v_out_name = v_name From trs_emp where c_id = '101'

And the PowerScript is like this:

//the stored procedure has output values but PowerScript has not declared the output values(-1)
 string ls_return
       Declare lpr_P004  procedure  For up_trs_003;
       Execute  lpr_P004;
       messagebox('After the stored procedure is executed',"sqldbcode    "+ string(sqlca.sqldbcode) + "      errtext: " + sqlca.sqlerrtext + "    Result" +ls_return)
       fetch lpr_P004 into :ls_return; 
       messagebox('After the result is fetched',"sqldbcode    "+ string(sqlca.sqldbcode) + "      errtext: " + sqlca.sqlerrtext + "    Result" +ls_return)
       Close lpr_P004;
       commit using sqlca;

//the stored procedure has output values but PowerScript has not declared the output values(-1)

string ls_return

Declare lpr_P004 procedure For up_trs_003;

Execute lpr_P004;

messagebox('After the stored procedure is executed',"sqldbcode "+ string(sqlca.sqldbcode) + " errtext: " + sqlca.sqlerrtext + " Result" +ls_return)

fetch lpr_P004 into :ls_return;

messagebox('After the result is fetched',"sqldbcode "+ string(sqlca.sqldbcode) + " errtext: " + sqlca.sqlerrtext + " Result" +ls_return)

Close lpr_P004;

commit using sqlca;

When the above PowerScript is
executed in PowerBuilder, the result is like
this

When the above PowerScript is
executed in PowerServer, the result is like
this

For Execute

SQLSTATE = 42000
Microsoft SQL Server Native Client 11.0
Procedure or function 'up_trs_003' expects parameter '@v_out_name', 
which was not supplied.

SQLSTATE = 42000

Microsoft SQL Server Native Client 11.0

Procedure or function 'up_trs_003' expects parameter '@v_out_name',

which was not supplied.

Sqldbcode = 0       
--Indicates execution is successful

1 2	Sqldbcode = 0 --Indicates execution is successful

For Fetch

sqldbcode  =  0
sqlerrtext: Cursor is not open

1 2	sqldbcode = 0 sqlerrtext: Cursor is not open

sqldbcode  =   0
sqlerrtext: Mismatch between retrieve columns and fetch columns

1 2	sqldbcode = 0 sqlerrtext: Mismatch between retrieve columns and fetch columns

Document get from Powerbuilder help

Thank you for watching.

Powerbuilder Documentation 2022

August 21, 2023

Was this article helpful?

Connection Information

Stored procedure parameter – PB Docs 2022

Stored procedure parameter

My Website Links