{"id":638,"date":"2025-07-28T18:25:12","date_gmt":"2025-07-28T18:25:12","guid":{"rendered":"https:\/\/mouryasolutions.in\/blog\/?p=638"},"modified":"2025-07-28T18:25:39","modified_gmt":"2025-07-28T18:25:39","slug":"how-to-return-any-sql-server-row-as-json-using-a-stored-procedure","status":"publish","type":"post","link":"https:\/\/mouryasolutions.in\/blog\/how-to-return-any-sql-server-row-as-json-using-a-stored-procedure\/","title":{"rendered":"How to Return Any SQL Server Row as JSON Using a Stored Procedure"},"content":{"rendered":"<div class=\"post-content\">\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2>\ud83d\udd0d SQL Server Trick: Returning a Row as JSON Using a Stored Procedure<\/h2>\n\n\n\n<p>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 <code>FOR JSON PATH<\/code>. But what if you want a <strong>dynamic stored procedure<\/strong> that can return <strong>any row from any table<\/strong> as a JSON object?<\/p>\n\n\n\n<p>Let\u2019s walk through a stored procedure that does exactly that.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3>\ud83d\udce6 What It Does<\/h3>\n\n\n\n<p>This stored procedure, <code>GetRowAsJson<\/code>, allows you to retrieve a single row from any table by its primary key and return it as a <strong>JSON object<\/strong>.<\/p>\n\n\n\n<p><strong>Parameters:<\/strong><\/p>\n\n\n\n<ul>\n<li><code>@table_name<\/code> \u2014 The name of the table to query.<\/li>\n\n\n\n<li><code>@pk_column<\/code> \u2014 The name of the primary key column (or a unique column).<\/li>\n\n\n\n<li><code>@pk_value<\/code> \u2014 The value of the primary key to filter on.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3>\ud83e\udde0 The Stored Procedure<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE PROCEDURE GetRowAsJson\n    @table_name NVARCHAR(128),\n    @pk_column NVARCHAR(128),\n    @pk_value NVARCHAR(128)\nAS\nBEGIN\n    DECLARE @sql NVARCHAR(MAX);\n    SET @sql = N'SELECT * FROM ' + QUOTENAME(@table_name) +\n               N' WHERE ' + QUOTENAME(@pk_column) + N' = @val FOR JSON PATH, WITHOUT_ARRAY_WRAPPER';\n\n    EXEC sp_executesql @sql, N'@val NVARCHAR(128)', @val = @pk_value;\nEND;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3>\u2699\ufe0f How It Works<\/h3>\n\n\n\n<ol>\n<li><strong>Dynamic SQL Construction<\/strong>:<br>It builds a <code>SELECT<\/code> statement using <code>QUOTENAME<\/code> to prevent SQL injection and syntax errors.<\/li>\n\n\n\n<li><strong>Parameter Binding<\/strong>:<br>It uses <code>sp_executesql<\/code> with a parameterized query (<code>@val<\/code>) to safely pass in the primary key value.<\/li>\n\n\n\n<li><strong>JSON Output<\/strong>:<br>By using <code>FOR JSON PATH, WITHOUT_ARRAY_WRAPPER<\/code>, the result is a single JSON object instead of an array.<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3>\ud83e\uddea Example Usage<\/h3>\n\n\n\n<p>Suppose you have a table named <code>Users<\/code> with a primary key column <code>UserId<\/code>. To get the row where <code>UserId = 42<\/code> as JSON:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>EXEC GetRowAsJson \n    @table_name = 'Users',\n    @pk_column = 'UserId',\n    @pk_value = '42';\n<\/code><\/pre>\n\n\n\n<p><strong>Output:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>{\n  \"UserId\": 42,\n  \"Username\": \"jdoe\",\n  \"Email\": \"jdoe@example.com\"\n}\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3>\ud83d\udd12 Security Tip<\/h3>\n\n\n\n<p>While <code>QUOTENAME<\/code> and <code>sp_executesql<\/code> reduce SQL injection risks, <strong>avoid exposing this procedure directly<\/strong> to untrusted users. Consider wrapping it with application logic that enforces table\/column restrictions.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3>\u2705 Use Cases<\/h3>\n\n\n\n<ul>\n<li>Building REST APIs directly from SQL Server<\/li>\n\n\n\n<li>Generating JSON for frontend JavaScript apps<\/li>\n\n\n\n<li>Data export tools or dashboards<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3>\ud83d\ude80 Conclusion<\/h3>\n\n\n\n<p>With just a few lines of T-SQL, you can add flexible JSON output capabilities to your SQL Server database. <code>GetRowAsJson<\/code> makes it easy to fetch data dynamically and serve it in a modern, API-friendly format.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n<\/div><!-- AddThis Advanced Settings generic via filter on the_content --><!-- AddThis Share Buttons generic via filter on the_content -->","protected":false},"excerpt":{"rendered":"<p>\ud83d\udd0d SQL Server Trick: Returning a Row as JSON Using<a href=\"https:\/\/mouryasolutions.in\/blog\/how-to-return-any-sql-server-row-as-json-using-a-stored-procedure\/\">Read More<i class=\"fa fa-long-arrow-right\" aria-hidden=\"true\"><\/i><\/a><!-- AddThis Advanced Settings generic via filter on get_the_excerpt --><!-- AddThis Share Buttons generic via filter on get_the_excerpt --><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[47],"tags":[],"_links":{"self":[{"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/posts\/638"}],"collection":[{"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/comments?post=638"}],"version-history":[{"count":2,"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/posts\/638\/revisions"}],"predecessor-version":[{"id":640,"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/posts\/638\/revisions\/640"}],"wp:attachment":[{"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/media?parent=638"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/categories?post=638"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/tags?post=638"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}