MSSQL MCP Server

Instructions

Implementation Reference

• src/tools/UpdateDataTool.ts:27-63 (handler)

  The main handler function that performs the SQL UPDATE operation using parameterized queries for security, validates the WHERE clause, and returns success/failure with rows affected.

  async run(params: any) {
    let query: string | undefined;
    try {
      const { tableName, updates, whereClause } = params;
      
      // Basic validation: ensure whereClause is not empty
      if (!whereClause || whereClause.trim() === '') {
        throw new Error("WHERE clause is required for security reasons");
      }
  
      const request = new sql.Request();
      
      // Build SET clause with parameterized queries for security
      const setClause = Object.keys(updates)
        .map((key, index) => {
          const paramName = `update_${index}`;
          request.input(paramName, updates[key]);
          return `[${key}] = @${paramName}`;
        })
        .join(", ");
  
      query = `UPDATE ${tableName} SET ${setClause} WHERE ${whereClause}`;
      const result = await request.query(query);
      
      return {
        success: true,
        message: `Update completed successfully. ${result.rowsAffected[0]} row(s) affected`,
        rowsAffected: result.rowsAffected[0],
      };
    } catch (error) {
      console.error("Error updating data:", error);
      return {
        success: false,
        message: `Failed to update data ${query ? ` with '${query}'` : ''}: ${error}`,
      };
    }
  }

• src/tools/UpdateDataTool.ts:8-25 (schema)

  Input schema defining required parameters: tableName (string), updates (object of column-value pairs), whereClause (string for security).

  inputSchema = {
    type: "object",
    properties: {
      tableName: { 
        type: "string", 
        description: "Name of the table to update" 
      },
      updates: {
        type: "object",
        description: "Key-value pairs of columns to update. Example: { 'status': 'active', 'last_updated': '2025-01-01' }",
      },
      whereClause: { 
        type: "string", 
        description: "WHERE clause to identify which records to update. Example: \"genre = 'comedy' AND created_date <= '2025-07-05'\"" 
      },
    },
    required: ["tableName", "updates", "whereClause"],
  } as any;

• src/index.ts:109-113 (registration)

  Registers the updateDataTool instance in the list of available tools returned by ListToolsRequest (in non-readonly mode).

  server.setRequestHandler(ListToolsRequestSchema, async () => ({
    tools: isReadOnly
      ? [listTableTool, readDataTool, describeTableTool] // todo: add searchDataTool to the list of tools available in readonly mode once implemented
      : [insertDataTool, readDataTool, describeTableTool, updateDataTool, createTableTool, createIndexTool, dropTableTool, listTableTool], // add all new tools here
  }));

• src/index.ts:126-127 (registration)

  Dispatches calls to the update_data tool by invoking its run method in the CallToolRequest handler switch statement.

  case updateDataTool.name:
    result = await updateDataTool.run(args);

• src/index.ts:83-83 (registration)

  Instantiates the UpdateDataTool class for use in the MCP server.

  const updateDataTool = new UpdateDataTool();