{"id":4525,"date":"2025-04-21T08:30:01","date_gmt":"2025-04-21T15:30:01","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=4525"},"modified":"2025-04-20T18:53:35","modified_gmt":"2025-04-21T01:53:35","slug":"predictable-llm-output-with-sp_invoke_external_rest_endpoint","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/predictable-llm-output-with-sp_invoke_external_rest_endpoint\/","title":{"rendered":"Predictable LLM results with Structured Output and sp_invoke_external_rest_endpoint"},"content":{"rendered":"<p>OpenAI recently introduced a powerful new feature for developers: <a href=\"https:\/\/openai.com\/index\/introducing-structured-outputs-in-the-api\/\">structured output<\/a> using JSON Schema via the <code>response_format<\/code> parameter. This makes it possible to request responses from a GPT-4o model that strictly match a given schema\u2014no free-text, no guesswork.\nIf you&#8217;re working with Azure SQL, this is a game-changer. Combined with the <code>sp_invoke_external_rest_endpoint<\/code> stored procedure and SQL Server&#8217;s built-in JSON functions, you can now:<\/p>\n<ul>\n<li>Call an LLM directly from T-SQL<\/li>\n<li>Receive output that strictly matches a schema you define<\/li>\n<li>Parse that output into a SQL table<\/li>\n<\/ul>\n<h2>What Is Structured Output?<\/h2>\n<p>By default, LLMs like GPT-4 return free-form text\u2014which can be difficult to parse reliably. With structured output, you provide a JSON Schema along with your request, and the model is implicitly instructed to generate a response that strictly adheres to that format. For example, if you want to have an output that would look like the following result:<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/04\/Screenshot-2025-04-18-145350.png\"><img decoding=\"async\" class=\"aligncenter wp-image-4529 size-full\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/04\/Screenshot-2025-04-18-145350.png\" alt=\"Image Screenshot 2025 04 18 145350\" width=\"1018\" height=\"215\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/04\/Screenshot-2025-04-18-145350.png 1018w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/04\/Screenshot-2025-04-18-145350-300x63.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/04\/Screenshot-2025-04-18-145350-768x162.png 768w\" sizes=\"(max-width: 1018px) 100vw, 1018px\" \/><\/a><\/p>\n<p>You have to provide the following JSON schema:<\/p>\n<pre class=\"prettyprint language-json\"><code class=\"language-sql\">declare @js nvarchar(max) = N'{\r\n    \"type\": \"json_schema\",\r\n    \"json_schema\": {\r\n        \"name\": \"products\",\r\n        \"strict\": true,\r\n        \"schema\": {\r\n            \"type\": \"object\",\r\n            \"properties\": {\r\n                \"products\": {\r\n                    \"type\": \"array\",\r\n                    \"items\": {\r\n                        \"type\": \"object\",\r\n                        \"properties\": {\r\n                            \"result_position\": {\r\n                                \"type\": \"number\"\r\n                            },\r\n                            \"id\": {\r\n                                \"type\": \"number\"\r\n                            },\r\n                            \"description\": {\r\n                                \"type\": \"string\",\r\n                                \"description\": \"a brief and summarized description of the product, no more than ten words\"\r\n                            },                            \r\n                            \"thoughts\": {\r\n                                \"type\": \"string\",\r\n                                \"description\": \"explanation of why the product has been chosen\"\r\n                            }\r\n                        },\r\n                        \"required\": [\r\n                            \"result_position\",\r\n                            \"id\",                            \r\n                            \"description\",                            \r\n                            \"thoughts\"                            \r\n                        ],\r\n                        \"additionalProperties\": false\r\n                    }\r\n                }\r\n            },\r\n            \"required\": [\"products\"],\r\n            \"additionalProperties\": false\r\n        }        \r\n    }        \r\n}'<\/code><\/pre>\n<h2>How to Use It in Azure SQL<\/h2>\n<p>Nothing real new under the sun here. Just use the usual <code>sp_invoke_external_rest_endpoint<\/code> to call your favorite LLM (as long as it supports structured output) and provide the JSON Schema in the <code>response_format<\/code> property in the sent JSON payload:<\/p>\n<pre class=\"prettyprint language-sql\"><code class=\"language-sql\">set @prompt = json_modify(@prompt, '$.response_format', json_query(@js))<\/code><\/pre>\n<p>then make the call:<\/p>\n<pre class=\"prettyprint language-sql\"><code class=\"language-sql\">declare @retval int, @response nvarchar(max);\r\nexec @retval = sp_invoke_external_rest_endpoint\r\n    @url = '&lt;OPENAI_URL&gt;\/openai\/deployments\/gpt-4o\/chat\/completions?api-version=2024-08-01-preview',\r\n    @headers = '{\"Content-Type\":\"application\/json\"}',\r\n    @method = 'POST',\r\n    @credential = [&lt;OPENAI_URL&gt;],\r\n    @timeout = 120,\r\n    @payload = @prompt,\r\n    @response = @response output\r\n    with result sets none;<\/code><\/pre>\n<p>and then all you have to do is take the resulting output and parse it into a table:<\/p>\n<pre class=\"prettyprint language-sql\"><code class=\"language-sql\">select \r\n    sr.* \r\nfrom \r\n    #r\r\ncross apply\r\n    openjson(response, '$.result.choices[0].message') with (\r\n        content nvarchar(max) '$.content'\r\n    ) m\r\ncross apply\r\n    openjson(m.content, '$.products') with (\r\n        result_position int,\r\n        id int,        \r\n        [description] nvarchar(max),\r\n        thoughts nvarchar(max)\r\n    ) as sr<\/code><\/pre>\n<p>Pretty easy!<\/p>\n<h2>Why This Is a Big Deal<\/h2>\n<p>This structured approach solves a core problem: <strong>predictability<\/strong>. In fact, one of the biggest challenges when working with large language models is the unpredictability of their responses. While free-form text might be easy for humans to understand, it&#8217;s often inconsistent and unreliable when you need to process it programmatically. If you\u2019re trying to build systems that depend on LLM-generated content\u2014especially within structured environments like databases\u2014this lack of consistency becomes a serious bottleneck.<\/p>\n<p class=\"\" data-start=\"520\" data-end=\"989\">Why? Because handling that unpredictability in code is hard. You end up writing custom parsing logic, regular expressions, or brittle string manipulation scripts that are difficult to maintain and easy to break. Every time the model returns a slightly different phrasing or changes the order of elements, your code risks falling apart. It creates technical debt fast and slows down development, making it harder to build reliable, production-grade systems on top of AI.<\/p>\n<p class=\"\" data-start=\"991\" data-end=\"1371\">That\u2019s where structured output changes the game. By supplying a JSON Schema along with your prompt, you\u2019re telling the model exactly what format the output should take. And with <code data-start=\"1169\" data-end=\"1183\">strict: true<\/code>, the model will stick to that structure rigidly. No more guessing what the model meant. No more fragile parsing logic. Just clean, well-formed JSON that you can immediately trust and use.<\/p>\n<p class=\"\" data-start=\"1373\" data-end=\"1797\">For developers working with Azure SQL or SQL Server, this predictability is powerful. You can take the JSON result from the model and easily map it into a table using SQL Server\u2019s native JSON functions. This means you can bring AI insights directly into your SQL workflows\u2014filtering, aggregating, joining with other datasets\u2014just like any other data. It effectively closes the loop between natural language and structured data processing.<\/p>\n<h2>What if you&#8217;re not using OpenAI models?<\/h2>\n<p>What if you want to host a LLM model on your own? Can you use structured output in that case? Then answer is most likely &#8220;yes&#8221;, provided that you are serving an LLM model capable of structured output. Then, inference servers like Ollama or vLLM all added support to structured output to their API:<\/p>\n<ul>\n<li><a href=\"https:\/\/ollama.com\/blog\/structured-outputs\">Ollama Structured Outputs<\/a><\/li>\n<li><a href=\"https:\/\/docs.vllm.ai\/en\/latest\/features\/structured_outputs.html\">vLLM Structured Outputs<\/a><\/li>\n<\/ul>\n<p>The way you&#8217;ll notify the model that you want to have the results returned using a specified JSON schema is different based on the inference server you&#8217;re using, but the principle is similar to the one described for OpenAI.<\/p>\n<h2>Final Thoughts<\/h2>\n<p>The combination of structured output and Azure SQL\u2019s REST and JSON support unlocks an entirely new kind of intelligent data workflow. With this powerful integration, you can build natural-language interfaces on top of your data, allowing for more intuitive and user-friendly interactions. Additionally, you can enrich your reports with AI-generated insights, providing deeper and more meaningful analysis. Furthermore, you can generate summaries, tags, classifications, and more\u2014all directly from SQL, streamlining your data processes and enhancing overall efficiency.<\/p>\n<p>Check out the working sample: <a href=\"https:\/\/github.com\/Azure-Samples\/azure-sql-db-chatbot\">https:\/\/github.com\/Azure-Samples\/azure-sql-db-chatbot\u00a0<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>OpenAI recently introduced a powerful new feature for developers: structured output using JSON Schema via the response_format parameter. This makes it possible to request responses from a GPT-4o model that strictly match a given schema\u2014no free-text, no guesswork. If you&#8217;re working with Azure SQL, this is a game-changer. Combined with the sp_invoke_external_rest_endpoint stored procedure and [&hellip;]<\/p>\n","protected":false},"author":24720,"featured_media":4531,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[601,1],"tags":[590,29,659],"class_list":["post-4525","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-ai","category-azure-sql","tag-ai","tag-json","tag-structured-output"],"acf":[],"blog_post_summary":"<p>OpenAI recently introduced a powerful new feature for developers: structured output using JSON Schema via the response_format parameter. This makes it possible to request responses from a GPT-4o model that strictly match a given schema\u2014no free-text, no guesswork. If you&#8217;re working with Azure SQL, this is a game-changer. Combined with the sp_invoke_external_rest_endpoint stored procedure and [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/4525","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/24720"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=4525"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/4525\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/4531"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=4525"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=4525"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=4525"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}