A procedure should reject a bad JSON request before it changes a row. Validating JSON parameters at the entry point keeps missing keys and wrong types from failing halfway through a transaction.
Start Validating JSON Parameters With the Document Shape
ISJSON checks syntax. On recent SQL Server versions, its OBJECT type constraint also distinguishes an object from an array or scalar. Do that check before JSON_VALUE or JSON_PATH_EXISTS calls. An invalid document should receive one clear error, not a chain of parser messages from later code. I also set a reasonable input length in the procedure contract. A valid but huge document can still be a poor request for a single-row operation. Define whether duplicate keys are allowed; ISJSON does not prove a business schema.
DECLARE @payload nvarchar(max) = N'{"customerId":42,"quantity":3}';
SELECT ISJSON(@payload, OBJECT) AS is_object;Name Every Required Key
JSON_PATH_EXISTS checks whether a path exists. It does not prove that the value has the expected type or is non-null. Test required paths one by one, then extract values for conversion. Keep path names in one documented contract so application and database teams agree. A typo in a path can otherwise look like an optional field that is always absent. I prefer a small explicit validation block over a clever loop for a stable request shape. The goal is an error a caller can correct.
DECLARE @payload nvarchar(max) = N'{"customerId":42,"quantity":3}';
SELECT JSON_PATH_EXISTS(@payload, '$.customerId') AS has_customer_id,
JSON_PATH_EXISTS(@payload, '$.quantity') AS has_quantity,
JSON_VALUE(@payload, '$.customerId') AS customer_id_text;Convert Without Starting Work
Use TRY_CAST or TRY_CONVERT on extracted scalar text. Check ranges and business rules after type conversion. An integer quantity of zero is valid JSON and valid integer text, but it can still be invalid for an order. Do not start a transaction or insert a row before these checks complete. I have seen procedures validate the easiest key, perform an update, then discover the second key is missing. That turns an input error into a rollback exercise.
Remember that JSON_VALUE returns text. Large scalar values, nested objects, and arrays need deliberate handling with OPENJSON or JSON_QUERY. Define the expected shape instead of accepting whatever happens to fit in one function call.
Return One Useful Error
Collect each validation problem into a table variable or string list, then raise one error message listing the problems. Keep it concise enough for the application to show or log. Do not echo the entire JSON payload into an error because it can contain personal data. The sample payload below is missing quantity and sends customerId as text. This block is meant to fail: it ends with THROW error 50030, and the message lists both problems.
DECLARE @payload nvarchar(max) = N'{"customerId":"abc"}';
DECLARE @errors table (message nvarchar(200) NOT NULL);
IF ISJSON(@payload, OBJECT) <> 1
INSERT @errors VALUES (N'Expected a JSON object.');
ELSE
BEGIN
IF JSON_PATH_EXISTS(@payload, '$.customerId') <> 1
INSERT @errors VALUES (N'customerId is required.');
IF JSON_PATH_EXISTS(@payload, '$.quantity') <> 1
INSERT @errors VALUES (N'quantity is required.');
IF JSON_PATH_EXISTS(@payload, '$.customerId') = 1
AND TRY_CAST(JSON_VALUE(@payload, '$.customerId') AS int) IS NULL
INSERT @errors VALUES (N'customerId must be an integer.');
IF JSON_PATH_EXISTS(@payload, '$.quantity') = 1
AND TRY_CAST(JSON_VALUE(@payload, '$.quantity') AS int) IS NULL
INSERT @errors VALUES (N'quantity must be an integer.');
END;
DECLARE @error_text nvarchar(2048);
SELECT @error_text = STRING_AGG(message, N' ') FROM @errors;
IF @error_text IS NOT NULL THROW 50030, @error_text, 1;
Distinguish Missing From Invalid
A missing key, explicit JSON null, an empty string, and a nonnumeric string can all become NULL after extraction or conversion. If callers need distinct error messages, inspect key presence and value separately. JSON_PATH_EXISTS can report presence, while OPENJSON exposes value and type for finer checks. Be clear about optional fields and defaults. A default should not quietly replace an invalid value. I ask the API owner which cases must be rejected, then encode those cases in tests.
Check property names and case. JSON path matching has its own behavior; do not assume a database collation makes two property names interchangeable. Use one canonical spelling in the contract.
Validate Business Rules After Types
Valid JSON and valid integer text do not make a valid order. Check that customerId exists, quantity is within the allowed range, and any enumerated status belongs to the documented set. Keep these rules separate from syntax checks so the error message says what the caller should correct. I use the same validation block for test and production paths; a different "fast path" is how unsupported values slip through. For arrays, validate element count and every member before starting writes. OPENJSON WITH can project typed fields, but it still needs explicit checks for missing or invalid values.
Avoid returning a long echo of the submitted payload. Error text should name fields, expected types, and allowed ranges without exposing private data. A correlation ID can connect the application log to secured request details when support needs them.
Finish Validating JSON Parameters Before the Transaction
After validation passes, begin the transaction, perform the work, and handle errors with SET XACT_ABORT ON plus TRY…CATCH where the procedure owns the transaction. Validation against reference tables can race with concurrent changes, so enforce essential relationships with foreign keys and unique constraints too. The precheck gives a friendly message. The constraint protects the invariant. I do not remove database constraints because the JSON validator looks thorough.
What if the request is retried after a timeout? Include an idempotency key for operations that must not repeat, and validate it alongside the JSON fields. A perfectly parsed request can still be applied twice. I test both malformed input and a duplicate valid request. The first must leave no work behind; the second must return the intended existing outcome or a clear conflict. That is the full contract at the procedure boundary, not merely a call to ISJSON.
A validation error should be stable enough for an application to handle. Keep field names consistent and return a documented error number or structured result alongside the readable message. I test that the procedure stops before opening a transaction for every invalid input. If validation happens after a write begins, the error text is clear but the transaction design is still wrong.
Test Every Rule for Validating JSON Parameters
Send a valid object, malformed JSON, an array, missing keys, explicit nulls, wrong types, out-of-range numbers, and extra fields. Decide whether extra fields are ignored or rejected. Verify that every invalid request leaves tables unchanged and returns a useful message. Run the procedure under the application's permissions, not only as a DBA. A validation query that needs extra rights is an avoidable production surprise.
I keep the tests for validating JSON parameters beside the procedure definition. JSON feels flexible, but that flexibility is exactly why the database boundary needs a precise contract. A clear rejection early is kinder than a partial change followed by an opaque conversion error.
Related reading on this blog: SQL SERVER Performance: JSON vs XML and STRING_ESCAPE() for JSON: String Escape.
JSON validation is not a late error handler, it is the first step of the procedure.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
