Call stored procedures with output parameters
As you know MBS Xojo SQL Plugin can execute stored procedures. For that you normally just pass the name of the stored procedure instead of a SQL command. The plugin than queries the details for the stored procedure like the input and output parameters. You can set input parameters and call execute. After you got all the result sets read, the last data package from the server brings the return code and the output parameter values. Check this sample code:
Sub TestStoredProcedure(con as SQLConnectionMBS)
// the stored procedure on the server
'CREATE PROC PR_DUMMY
'@ret_val INT OUTPUT
'AS
'SET NOCOUNT ON
'SET @ret_val = 9999
'SELECT 1
'return 1111
// create new command with just name of stored procedure
dim cmd as new SQLCommandMBS(con, "PR_DUMMY")
// query parameters from server
cmd.prepare
// set an input parameter if needed
'cmd.Param("test").setAsLong 1234
cmd.Execute
dim x as integer = cmd.RowsAffected
// get all result sets
while cmd.isResultSet
// get next record
while cmd.FetchNext
// process record
wend
wend
// get output parameter
dim pVal as integer = cmd.Param("ret_val").asLong
// get return value
dim retVal as integer = cmd.Param("RETURN_VALUE").asLong
Break // check values
End Sub