Procedure Parameters
The ProcedureParameters schema collection describes the stored procedure parameters.
Retrieving Stored Procedure Parameter Metadata
The ProcedureParameters schema collection contains information about the parameters of stored procedures.
To retrieve the ProcedureParameters schema collection, call the GetSchema method of the AzureDevOpsConnection class. Access the metadata in the DataTable object returned. The following example retrieves parameter information for all stored procedures:
C#
String connectionString = "AuthScheme=Basic;Organization=MyAzureDevOpsOrganization;Catalog=Project_dev;Schema=Project;PersonalAccessToken=MyPAT;"; using (AzureDevOpsConnection conn = new AzureDevOpsConnection(connectionString)) { conn.Open(); DataTable table = conn.GetSchema("ProcedureParameters"); foreach (DataRow row in table.Rows) { foreach (DataColumn col in table.Columns) { Console.WriteLine(col.ColumnName + "=" + row[col]); } } }
VB.NET
Dim connectionString As [String] = "User=AuthScheme=Basic;Organization=MyAzureDevOpsOrganization;Catalog=Project_dev;Schema=Project;PersonalAccessToken=MyPAT;" Using conn As New AzureDevOpsConnection(connectionString) conn.Open() Dim table As DataTable = conn.GetSchema("ProcedureParameters") For Each row As DataRow In table.Rows For Each col As DataColumn In table.Columns Console.WriteLine(col.ColumnName + "=" + row(col)) Next Next End Using
Columns Returned
The columns of the schema collection are the following:
Column Name | Data Type | Description |
SPECIFIC_CATALOG | System.String | The name of the database containing the stored procedure. |
SPECIFIC_SCHEMA | System.String | The schema that contains the stored procedure. |
SPECIFIC_NAME | System.String | The name of the stored procedure containing the parameter. |
PARAMETER_NAME | System.String | The name of the parameter. |
PARAMETER_MODE | System.String | Returns IN for an input parameter, OUT for an output parameter, or INOUT for parameters that can be both input and output parameters. |
ORDINAL_POSITION | System.Int32 | The sequence number of the parameter. |
DATA_TYPE | System.String | The data type name. |
CHARACTER_MAXIMUM_LENGTH | System.Int32 | The maximum length in characters. |
CHARACTER_SET_NAME | System.String | The name of the character set for a column with character data. |
NUMERIC_PRECISION | System.Int32 | The maximum number of digits in numeric data. |
NUMERIC_SCALE | System.Int32 | The column scale or number of digits to the right of the decimal point. |
DATETIME_PRECISION | System.Int32 | The precision in fractional seconds if the parameter type is datetime or smalldatetime. Otherwise, returns NULL. |
PROCEDURE_DESCRIPTION | System.String | A brief description of the procedure. |
PROVIDER_TYPE | System.Type | Indicates the appropriate data type dependent on the language you are executing in. |