Method ExecuteCustomStoredProcedure
ExecuteCustomStoredProcedure(String, String, List<DBParameterContract>, String, Boolean)
Executes a custom stored procedure
Declaration
CustomStoredProcedureResultContract ExecuteCustomStoredProcedure(string connectionId, string storedProcedureName, List<DBParameterContract> parameters, string commandType, bool addColumnNamesAsFirstRow)
Parameters
Type | Name | Description |
---|---|---|
System.String | connectionId | The connection id to identify your connection. See also ConnectionId |
System.String | storedProcedureName | The stored procedure name. Must start with UserProc_ |
System.Collections.Generic.List<DBParameterContract> | parameters | A list of database parameters. See also DBParameterContract |
System.String | commandType | The data retrieve command type DataRetrieveCommandTypeEnum |
System.Boolean | addColumnNamesAsFirstRow | If true, adds the column names as first row to the DataTableResult of the returned result |
Returns
Type | Description |
---|---|
CustomStoredProcedureResultContract | The custom stored procedure result.
|
Remarks
SQL Server, Oracle:
- Procedure name must start with UserProc_
- Named notation is used to pass parameters
- Put custom stored PL/SQL procedures into package dcispackage
- Before updating the InfoShare database, make a backup of the custom stored procedures. Otherwise you will loose them
Examples
This example calls a SQL Server custom stored procedure. The stored procedure has an input parameter and it returns all data from a user table (including the column headers). The result is returned in DataTableResult.
POST https://YourServerName/InfoShare/Json/UserTable/ExecuteCustomStoredProcedure
{
"connectionId": "ed52ab64-40a8-4c7b-b234-209bb5af9263",
"storedProcedureName": "UserProc_Test1",
"parameters": [
{ "Name":"pivUserTable", "Value":"UserTable_Test1", "Direction":"Input", "DataType":"NVarchar" }
],
"commandType": "ExecuteReader",
"addColumnNamesAsFirstRow": false
}
Response:
{
"ExecuteCustomStoredProcedureResult": {
"DataTableResult": [
["column1", "column2", "column3", "column4"],
["row1_col1", "row1_col2", "row1_col3", ""],
["row2_col1", "row2_col2", "", "row2_col4"],
["row5_1", "row5_col2", "row5_col3", "row5_col4"],
["row6__1", "row6_col2", "row6_col3", "row6_col4"]
],
"OutputParameterResult": [],
"ReturnValue": null,
"RowsAffected": "-1",
"ScalarResult": null
}
}
{
"connectionId": "5de008d5-7683-4ff4-9555-4bd74bb1e105",
"storedProcedureName": "UserProc_Test1",
"parameters": [
{ "Name":"pin1", "Value":"10", "Direction":"Input", "DataType":"Varchar2" },
{ "Name":"pin2", "Value":null, "Direction":"Output", "DataType":"Varchar2", "Size":"2" }
],
"commandType": "ExecuteNonQuery",
"addColumnNamesAsFirstRow": false
}
Response:
{
"ExecuteCustomStoredProcedureResult": {
"DataTableResult": [],
"OutputParameterResult": [
{"Name":"@pin2","Value":"20"}
],
"ReturnValue": null,
"RowsAffected": "-1",
"ScalarResult": null
}
}
Exceptions
Type | Condition |
---|---|
ServiceException | The connection id {connectionId} isn't valid or has expired (10008) |
ServiceException | Verification code is needed: Verification code not yet verified (10152) |
ServiceException | Stored procedure name {storedProcedureName} is invalid. It must start with UserProc_ (10159) |
ServiceException | Could not parse string {dataRetrieveCommandType} to a valid command type. Valid types are: {allowedDataRetrieveCommandTypes} (10160) |
ServiceException | Could not parse string {parameterDirectionType} to a valid parameter direction type. Valid types are: {allowedParameterDirectionTypes} (10161) |
ServiceException | Could not parse string {parameterDataType} to a valid parameter data type. Valid types are: {allowedParameterDataTypes} (10162) |