« FileMaker DevCon Vide… | Home | Adding Drag and Drop … »

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
07 10 18 - 08:59