SQL Server MCP

Instructions

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.)

Implementation Reference

• src/mssql_mcp/tools/stored_procs.py:77-140 (handler)

  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)}