SQL-AI-samples icon indicating copy to clipboard operation
SQL-AI-samples copied to clipboard

Add stored procedure and function tools with comprehensive test coverage

Open shubham070 opened this issue 6 months ago • 1 comments

🚀 Overview

This PR implements comprehensive support for SQL Server stored procedures and functions in the MCP server, including execution capabilities, metadata retrieval, and robust test coverage.

✨ Features Added

New MCP Tools (6 tools)

  • ExecuteStoredProcedure - Execute stored procedures with optional parameters
  • ExecuteFunction - Execute table-valued functions with optional parameters
  • ListProceduresAndFunctions - List all stored procedures and functions in the database
  • DescribeProcedureOrFunction - Get detailed metadata about specific procedures/functions
  • CreateProcedure - Creates a new stored procedure in the SQL Database
  • CreateFunction - -"Creates a new function in the SQL Database

Comprehensive Test Coverage

  • 18 Unit Tests - Fast, isolated tests without database dependencies
  • Integration Tests - End-to-end testing with real SQL Server (existing tests preserved)
  • Test Documentation - Complete README with best practices and execution commands

🔧 Technical Implementation

Core Features

  • Parameter validation and sanitization for security
  • Support for both parameterized and parameterless execution
  • Comprehensive error handling and logging
  • Consistent return format following existing MCP patterns
  • Full metadata extraction including parameter definitions

Architecture Improvements

  • Clean separation between unit and integration tests
  • Follows Test Pyramid principle for optimal feedback cycles
  • Maintains existing code patterns and conventions
  • Zero breaking changes to existing functionality

🧪 Testing Strategy

Unit Tests (ToolsUnitTests.cs)

dotnet test --filter "FullyQualifiedName~ToolsUnitTests"
# ✅ 16 tests, ~1.2s execution time, no database required

shubham070 avatar Jul 18 '25 11:07 shubham070