Monthly Archives: July 2025

#0431 – SQL Server – Myths – Can a stored procedure have more than one OUTPUT variables?

2 Replies

It’s a fairly common practice to use the OUTPUT clause to return scalar output from a stored procedure. Today’s post is based on a question that I was asked by one of my colleagues related to the usage of OUTPUT clause:

“Can a stored procedure have more than one OUTPUT variables?”

The short answer is Yes! Not only that – it also possible to have non-scalar outputs from stored procedures. But more about that in another post.

Here’s a quick example of a stored procedure having more than one (1) OUTPUT variables. It’s a simple procedure that validates if the input parameters are NULL and subsequently adds two (2) integers. At the end, stored procedure returns 2 variables – the processing state and the result of the addition.

USE [tempdb];
GO
IF OBJECT_ID('dbo.proc_AddNumbersWithMultipleOutputs','P') IS NOT NULL
BEGIN
	DROP PROCEDURE [dbo].[proc_AddNumbersWithMultipleOutputs];
END
GO

CREATE PROCEDURE [dbo].[proc_AddNumbersWithMultipleOutputs]
	@intA INT,
	@intB INT,
	@isSuccessfullyProcessed BIT OUTPUT, --Output variable #1
	@resultC                 INT OUTPUT  --Output variable #2
AS 
BEGIN
	SET NOCOUNT ON;

	SET @isSuccessfullyProcessed = 0;
	SET @resultC                 = 0;

	BEGIN TRY
		IF ((@intA IS NULL) OR (@intB IS NULL))
			THROW 51000,'Input Parameters cannot be NULL',-1;

		SET @resultC = @intA + @intB;
		SET @isSuccessfullyProcessed = 1;
	END TRY
	BEGIN CATCH
		SET @isSuccessfullyProcessed = 0;
		SELECT ERROR_NUMBER(),
		       ERROR_MESSAGE(),
			   ERROR_SEVERITY(),
			   ERROR_STATE();
	END CATCH
END
GO

And here’s the procedure in action. The first query indicates successful execution. 

--In a separate window, run the following 
DECLARE @inputA  INT = 2; --Use NULL to throw exception
DECLARE @inputB  INT = 3; --Use NULL to throw exception
DECLARE @outputC INT;
DECLARE @processingState INT;

EXEC [dbo].[proc_AddNumbersWithMultipleOutputs] 
	@intA                    = @inputA,
    @intB                    = @inputB,
	@isSuccessfullyProcessed = @processingState OUTPUT,
	@resultC                 = @outputC OUTPUT;

SELECT @inputA          AS 'Input A', 
       @inputB          AS 'Input B',
	   @outputC         AS 'Result', 
	   @processingState AS 'IsTransactionSuccessfullyProcessed';
GO

The following query with NULL input parameters will result in an exception.

--In a separate window, run the following 
DECLARE @inputA  INT = 2; --Use NULL to throw exception
DECLARE @inputB  INT = NULL;
DECLARE @outputC INT;
DECLARE @processingState INT;

EXEC [dbo].[proc_AddNumbersWithMultipleOutputs] 
	@intA                    = @inputA,
    @intB                    = @inputB,
	@isSuccessfullyProcessed = @processingState OUTPUT,
	@resultC                 = @outputC OUTPUT;

SELECT @inputA          AS 'Input A', 
       @inputB          AS 'Input B',
	   @outputC         AS 'Result', 
	   @processingState AS 'IsTransactionSuccessfullyProcessed';
GO

I trust this simple demonstration will help you to write more effective T-SQL code.

Until we meet next time,

Be courteous. Drive responsibly.

#0430 – SQL Server – Tips: Working with JSON to build payload samples

Leave a reply

As a system integration architect, I often deal with generating & processing payloads in SQL server. In a previous post, I showed how to avoid JSON array wrappers when generating payloads and samples from data using T-SQL.

Based on the feedback received from a few colleagues of mine, I will be sharing some tips I use to build various JSON payload samples from test data. For these examples, we will be using the [AdventureWorks2022] sample database.

Nesting/Embedding JSONs

The true value of FOR JSON lies in the fact that it can return complex hierarchical data as nested JSON objects or arrays. In the example below, we have a single object corresponding to one (1) customer but they have multiple child orders – which are represented as a JSON array.

USE [AdventureWorks2022];
GO
SELECT [Customer].[FirstName]   AS [Name], 
       [Customer].[PhoneNumber] AS [Phone], 
	   [Customer].[City]        AS [City],
       [Orders].[SalesOrderID]  AS [OrderId],
	   [Orders].[OrderDate]     AS [OrderDate],
	   [Orders].[DueDate]       AS [ExpectedDeliveryDate]
FROM [Sales].[vIndividualCustomer] AS [Customer]
INNER JOIN [Sales].[SalesOrderHeader] AS [Orders] ON [Customer].[BusinessEntityID] = [Orders].[CustomerID]
WHERE [Customer].[BusinessEntityID] = 12630
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;
GO

{
	"Name": "Clayton",
	"Phone": "1 (11) 500 555-0145",
	"City": "Roncq",
	"Orders": [
		{
			"OrderId": 49271,
			"OrderDate": "2013-01-09T00:00:00",
			"ExpectedDeliveryDate": "2013-01-21T00:00:00"
		},
		{
			"OrderId": 54524,
			"OrderDate": "2013-08-16T00:00:00",
			"ExpectedDeliveryDate": "2013-08-28T00:00:00"
		},
		{
			"OrderId": 56797,
			"OrderDate": "2013-09-26T00:00:00",
			"ExpectedDeliveryDate": "2013-10-08T00:00:00"
		}
	]
}

Build complex JSON structures

As an extension to the thought process, suppose you want to embed a JSON into another object. Here’s a simple way to do that:

SELECT 
  JSON_QUERY(
    (SELECT '404' AS [HttpStatusCode], 
	        'Page not found' AS [Message]
     FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
  ) AS [ProcessingStatus],
  20 AS [TransactionId]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
GO

{
	"ProcessingStatus": {
		"HttpStatusCode": "404",
		"Message": "Page not found"
	},
	"TransactionId": 20
}

Similarly, you can even combine 2 JSON messages together:

SELECT 
  JSON_QUERY(
    (SELECT 'HttpTimeout' AS [name], 
	         '60'         AS [value] 
	 FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
    ) AS [ConfigSettings],
  JSON_QUERY(
    (SELECT 'SQLTwins' AS [username]
	 FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
	)
  ) AS [UserInfo]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

{
	"ConfigSettings": {
		"name": "HttpTimeout",
		"value": "60"
	},
	"UserInfo": {
		"username": "SQLTwins"
	}
}

I trust you will find these tips and samples useful. Do share if you have any tricks that you use when working with JSONs and payloads.

Until we meet next time,

Be courteous. Drive responsibly.

#0429 – SQL Server – Working with JSON using WITHOUT_ARRAY_WRAPPER

1 Reply

When working on system integrations, JSON is often the format of choice used to exchange data. As with any data contract, the clarity and structure of the schema matter. Default behaviors (like wrapping JSON into arrays) can complicate things when you’re expecting a single object. This is especially true when processing feeds or doing asynchronous operations.

I dabble a lot in data operations and often use SQL Server queries to build my JSON payloads. As we know, we can use the FOR JSON PATH clause to generate the output as a JSON.

USE [AdventureWorks2022];
GO
SELECT [FirstName], [LastName], [PhoneNumber], [City]
FROM [Sales].[vIndividualCustomer]
WHERE [BusinessEntityID] BETWEEN 1600 AND 1700
FOR JSON PATH;
GO

[
	{
		"FirstName": "Rebecca",
		"LastName": "Robinson",
		"PhoneNumber": "648-555-0100",
		"City": "Seaford"
	},
	{
		"FirstName": "David",
		"LastName": "Robinett",
		"PhoneNumber": "238-555-0100",
		"City": "Solingen"
	}
]

If you observe carefully, the output is a JSON array. This is true even if one (1) object is being returned – which then requires me to edit the payload manually. Here’s the example:

USE [AdventureWorks2022];
GO
SELECT [FirstName], [LastName], [PhoneNumber], [City]
FROM [Sales].[vIndividualCustomer]
WHERE [BusinessEntityID] = 1700
FOR JSON PATH;
GO

[
	{
		"FirstName": "Rebecca",
		"LastName": "Robinson",
		"PhoneNumber": "648-555-0100",
		"City": "Seaford"
	}
]

Introducing WITHOUT_ARRAY_WRAPPER

Starting SQL Server 2016, a new option (WITHOUT_ARRAY_WRAPPER) is now available. This option can be used with a single-row result to generate a single JSON object as output instead of an array with a single element.

Here’s the same example as above, but with the WITHOUT_ARRAY_WRAPPER clause applied.

USE [AdventureWorks2022];
GO
SELECT [FirstName], [LastName], [PhoneNumber], [City]
FROM [Sales].[vIndividualCustomer]
WHERE [BusinessEntityID] = 1700
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
GO

{
	"FirstName": "Rebecca",
	"LastName": "Robinson",
	"PhoneNumber": "648-555-0100",
	"City": "Seaford"
}

Practical use-cases

Below are some practical use-cases where the WITHOUT_ARRAY_WRAPPER option has been very handy for me:

  1. Creating Samples – for API requests, responses and writing documentation!
  2. Nesting/embedding JSON in another JSON
  3. Logging or auditing single events

Word of caution

If you use this option with a multiple-row result, no error will be returned. However, the resulting output is not valid JSON because of the multiple elements and the missing square brackets.

Call to Action

I trust you found this quick nugget helpful. Do let me know if you already knew about it before. I would be interested to read about the various cases in which you have used the FOR JSON PATH, WITHOUT_ARRARY_WRAPPER option.

Until we meet next time,

Be courteous. Drive responsibly.

Disclaimer: The [AdventureWorks2022] sample database (https://learn.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver17&tabs=ssms) has been used for all test data used in this post.

#0428 – SQL Server – Demystifying the CURRENT keyword: Why it exists and when to use it

Leave a reply

Recently, I was reviewing a T-SQL script and saw a ALTER DATABASE statement with the CURRENT keyword. It seems redundant at first glance. After all, if you are already connected to a database, why would you want to refer to it as CURRENT?

In this post, I try to uncover the purpose, possible use cases and potential mistakes that one can make when using the CURRENT keyword.

The Purpose

First, let’s understand the purpose of the CURRENT statement. Put very simply, the CURRENT keyword in the ALTER DATABASE statement refers to the database that you are connected to. Instead of hard-coding the database name, you can use CURRENT to make your script more portable and dynamic.

-- Instead of this:
ALTER DATABASE [SQLTwins] SET COMPATIBILITY_LEVEL = 150;

-- You can write:
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150;

This is especially useful in deployment pipelines, maintenance scripts, or reusable procedures where the database context may vary.

Typical Use-cases

Here are some practical scenarios where CURRENT keyword is very useful:

  1. Automated Scripts: Avoid hard-coding database names when deploying changes across multiple databases.
  2. Cross-Environment Compatibility: Whether you’re running the script on dev, test, or prod, CURRENT targets the database that you are connected to.
  3. Contained Databases: In Azure SQL Database or Managed Instances, where scripts run within a specific database context, CURRENT ensures the command targets the right scope.

CURRENT v/s DB_NAME()

You might wonder: why not dynamically fetch the database name using the very familiar DB_NAME() and build the statement?

DECLARE @dbName NVARCHAR(128) = DB_NAME();
EXEC('ALTER DATABASE [' + @dbName + '] SET COMPATIBILITY_LEVEL = 150');

While DB_NAME() works, it’s great for dynamic SQL. Dynamic SQL is often complicated to manage (esp. in deployment pipelines) and introduces potential quoting issues. CURRENT is cleaner, safer, and easier to read – more so for simple configuration changes.

Please consider

Before you start using CURRENT in your scripts, be on the lookout for the following common mistakes:

  1. Context Awareness: If your script runs in a multi-database environment, double-check that you’re in the right context before executing. Remember: CURRENT refers to the database you’re connected to, not necessarily the one you intend to modify.
  2. Permissions: CURRENT doesn’t bypass permission checks and the scripts would fail if your user is not allowed to ALTER the database
  3. Forgetting Test Coverage: Because CURRENT abstracts the database name, it’s easy to overlook edge cases in testing—especially when deploying across environments. Always validate that your script behaves as expected in all environments & configurations that you intend to use.
  4. Mixing with Dynamic SQL: Using CURRENT with dynamic SQL is unnecessary and can be confusing or redundant (unless it’s a code generator)
  5. The CURRENT keyword is fully supported for Azure SQL Database and Azure SQL Managed Instances. However, for on-premise installations SQL Server 2012 (11.x) and above (Why are you on a lower version anyway?)

In conclusion

The CURRENT keyword may seem trivial. However, it plays a subtle role in making T-SQL scripts more portable. It also contributes to scripts being context-aware and cloud-friendly. Whether you’re managing databases in Azure or on-prem, using ALTER DATABASE CURRENT can streamline your operations. It also reduces hard-coded dependencies.

Further Reading

Until we meet next time,

Be courteous. Drive responsibly.