The Situation

You have a SQL Server stored procedure that needs variable for use in a query, but you don’t want to pass it into the stored procedure as a parameter, and you don’t want to hard-code it. Instead you have the value stored in a table and want to retrieve it from there. There is the possibility that the value in the table could be NULL.

I ran into this situation when creating some dynamic sql in a stored procedure. In some instances, the stored proc needed to access a table in Server1, but in other situations, it needed to access a table with the same name on Server2. Because servers are sometimes retired or replaced, hard-coding the server name would cause errors at a later date. The server names were made configurable by adding a value to an ApplicationSettings table:

CREATE TABLE [ApplicationSettings](
	[SettingName] [nvarchar](50) NOT NULL,
	[SettingValue] [nvarchar](max) NULL
);
INSERT INTO [ApplicationSettings] (SettingName, SettingValue)
VALUES ('GeneralInfoServer', 'SERVER2')

Retrieving that value so it can be added to the dynamic sql is simple:

DECLARE @Server VARCHAR(50) = 'Server1'; -- Default value
SET @Server = (SELECT SettingValue 
		FROM ApplicationSettings 
		WHERE SettingName = 'GeneralInfoServer');

 

The problem is that someone could come along and delete that record from ApplicationSettings. In that case, the SELECT statement above would return NULL, and the default value of @Server would be set to NULL, causing an error when running the stored proc.

A Solution

T-SQL has the ISNULL(expression, alternative_value) function, which is usually used like this:

DECLARE @Server VARCHAR(50) = 'Server1'; -- Default value
SET @Server = (SELECT ISNULL(SettingValue, 'SERVER1') 
		FROM ApplicationSettings 
		WHERE SettingName = 'GeneralInfoServer');

 

However, this only works if the record exists and the SettingValue is NULL. It still returns a null value if the record doesn’t exist.

There are more complicated ways of writing the query, but simplest way to get the default value is to move ISNULL to the outside of the select query like this:

DECLARE @Server VARCHAR(50) = 'Server1'; -- Default value
SET @Server = ISNULL((SELECT SettingValue 
			FROM ApplicationSettings 
			WHERE SettingName = 'GeneralInfoServer'), @Server);

 

This statement says, “Retrieve the value of ‘GeneralInfoServer’, but if that record doesn’t exist, return the default value of @Server.”

But we can make one more improvement. What if the record exists but the value is NULL? Combine the last 2 queries like this:

DECLARE @Server VARCHAR(50) = 'Server1'; -- Default value
SET @Server = ISNULL((SELECT ISNULL(SettingValue, @Server) 
			FROM ApplicationSettings 
			WHERE SettingName = 'GeneralInfoServer'), @Server);

 

Of course this could STILL fail if the default value is not valid.