MCP Tool

An MCP tool exposes a SQL Server stored procedure as a callable tool. Metadata, such as name, input schema, and procedure name, is registered in the system.

Input can be parameter-based (mapping directly to procedure parameters), raw JSON, or omitted entirely for tools that do not require input.

Output can be plain text, raw JSON, or a result set that is automatically serialized to JSON.

Claims

If the MCP server has security configured, claims contain JSON-serialized data that identifies the caller.

[{
  "name": "xxx",
  "value": "yyy"
}, ... ]

Example Tool: User Details

Demonstrates use of the ErrorMessage column, implicit JSON output, and the _RawJSON suffix for explicit JSON output.

// Schema for UserId
{"type": "integer"}

CREATE OR ALTER PROCEDURE Example.User_DetailsTool
	@UserId int = NULL,

	@Claims nvarchar(MAX) = NULL
AS
BEGIN
	IF NOT EXISTS (SELECT * FROM SoftadminApi.[User] WHERE UserId = @UserId)
	BEGIN
		SELECT CONCAT('User with id ', @UserId, ' not found') AS ErrorMessage;
		RETURN -1;
	END;

	SELECT
		U.UserId,
		U.Username,
		U.UsernameFirst,
		U.UsernameLast,
		U.UserEmail,
		U.RoleId,
		U.IsAdministrator,
		U.LanguageId,
		U.IsEnabled,
		CONVERT(bit, IIF(U.PasswordHash IS NOT NULL, 1, 0)) AS HasPassword,
		U.LastSuccessfulLogin,
		(
			SELECT
				F.FunctionId,
				F.FunctionName
			FROM
				SoftadminApi.User_Functions(@UserId) UF
				JOIN SoftadminApi.Functions(1) F ON F.FunctionID = UF.FunctionID
			FOR JSON PATH
		) AS Functions_RawJSON
	FROM
		SoftadminApi.[User] U
	WHERE
		U.UserId = @UserId;
END;

Example: Function List

Demonstrates using a JSON array as input.

// Schema for FunctionIds
{"type": "array", "items": { "type": "integer" }, "description": "Filters the list"}

CREATE OR ALTER PROCEDURE Example.Function_List
	@FunctionIds nvarchar(MAX) = NULL,

	@Claims nvarchar(MAX) = NULL
AS
BEGIN
	SELECT
		F.FunctionId,
		F.FunctionName,
		F.FunctionAlias,
		F.FunctionDescription
	FROM
		SoftadminApi.Functions(NULL) F
	WHERE
		(@FunctionIds IS NULL OR F.FunctionId IN (
			SELECT value
			FROM OPENJSON(@FunctionIds) WITH (value INT '$')
		))
	ORDER BY
		F.SortOrder,
		F.FunctionId;
END;