describe_stored_proc
Retrieve parameter details for SQL Server stored procedures to understand input requirements and data types before execution.
Instructions
Get parameter information for a stored procedure.
Args:
procedure: Procedure name, optionally with schema (e.g., 'dbo.sp_GetUser' or 'sp_GetUser')
Returns:
Dictionary with:
- procedure: Full procedure name (schema.name)
- parameters: List of parameter info (name, type, direction, etc.)Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| procedure | Yes |
Output Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
No arguments | |||
Implementation Reference
- The handler function that implements the logic for the 'describe_stored_proc' MCP tool. It parses the procedure name, queries the database for parameter information from INFORMATION_SCHEMA.PARAMETERS, formats the results, and handles errors. The @mcp.tool() decorator registers it as an MCP tool. The docstring provides input/output schema details.
@mcp.tool() def describe_stored_proc(procedure: str) -> dict[str, Any]: """Get parameter information for a stored procedure. Args: procedure: Procedure name, optionally with schema (e.g., 'dbo.sp_GetUser' or 'sp_GetUser') Returns: Dictionary with: - procedure: Full procedure name (schema.name) - parameters: List of parameter info (name, type, direction, etc.) """ try: manager = get_connection_manager() # Parse schema.procedure format if "." in procedure: parts = procedure.split(".", 1) schema = parts[0] proc_name = parts[1] else: schema = "dbo" proc_name = procedure query = """ SELECT PARAMETER_NAME as [name], DATA_TYPE as [type], PARAMETER_MODE as [direction], CHARACTER_MAXIMUM_LENGTH as [max_length], NUMERIC_PRECISION as [precision], NUMERIC_SCALE as [scale], ORDINAL_POSITION as [position] FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA = %s AND SPECIFIC_NAME = %s ORDER BY ORDINAL_POSITION """ rows = manager.execute_query(query, (schema, proc_name)) parameters = [] for row in rows: param_info: dict[str, Any] = { "name": row["name"] or "(return value)", "type": row["type"], "direction": row["direction"] or "IN", } if row["max_length"]: param_info["max_length"] = row["max_length"] if row["precision"]: param_info["precision"] = row["precision"] if row["scale"]: param_info["scale"] = row["scale"] parameters.append(param_info) return { "procedure": f"{schema}.{proc_name}", "parameters": parameters, } except Exception as e: logger.error(f"Error describing stored procedure {procedure}: {e}") return {"error": str(e)}