How to Return Any SQL Server Row as JSON Using a Stored Procedure

---

🔍 SQL Server Trick: Returning a Row as JSON Using a Stored Procedure

In modern applications, especially those involving APIs or frontend frameworks, JSON has become the de facto format for data exchange. Fortunately, SQL Server makes it easy to return query results in JSON format with FOR JSON PATH. But what if you want a dynamic stored procedure that can return any row from any table as a JSON object?

Let’s walk through a stored procedure that does exactly that.

---

📦 What It Does

This stored procedure, GetRowAsJson, allows you to retrieve a single row from any table by its primary key and return it as a JSON object.

Parameters:

• @table_name — The name of the table to query.
• @pk_column — The name of the primary key column (or a unique column).
• @pk_value — The value of the primary key to filter on.

---

🧠 The Stored Procedure

CREATE PROCEDURE GetRowAsJson
    @table_name NVARCHAR(128),
    @pk_column NVARCHAR(128),
    @pk_value NVARCHAR(128)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'SELECT * FROM ' + QUOTENAME(@table_name) +
               N' WHERE ' + QUOTENAME(@pk_column) + N' = @val FOR JSON PATH, WITHOUT_ARRAY_WRAPPER';

    EXEC sp_executesql @sql, N'@val NVARCHAR(128)', @val = @pk_value;
END;

---

⚙️ How It Works

1. Dynamic SQL Construction:
   It builds a SELECT statement using QUOTENAME to prevent SQL injection and syntax errors.
2. Parameter Binding:
   It uses sp_executesql with a parameterized query (@val) to safely pass in the primary key value.
3. JSON Output:
   By using FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, the result is a single JSON object instead of an array.

---

🧪 Example Usage

Suppose you have a table named Users with a primary key column UserId. To get the row where UserId = 42 as JSON:

EXEC GetRowAsJson 
    @table_name = 'Users',
    @pk_column = 'UserId',
    @pk_value = '42';

Output:

{
  "UserId": 42,
  "Username": "jdoe",
  "Email": "jdoe@example.com"
}

---

🔒 Security Tip

While QUOTENAME and sp_executesql reduce SQL injection risks, avoid exposing this procedure directly to untrusted users. Consider wrapping it with application logic that enforces table/column restrictions.

---

✅ Use Cases

• Building REST APIs directly from SQL Server
• Generating JSON for frontend JavaScript apps
• Data export tools or dashboards

---

🚀 Conclusion

With just a few lines of T-SQL, you can add flexible JSON output capabilities to your SQL Server database. GetRowAsJson makes it easy to fetch data dynamically and serve it in a modern, API-friendly format.

---