Setting a default value for a stored proc select statement

Question

I am creating a stored proc that selects a value from a table and uses it in another procedure. If the first value that is searched doesn’t exist I need it to use a default value. I’m new to stored procs so I’m not sure of the best practices.

Here is the first select statement which may or may not return a value. If it doesn’t return a value I need to set the “@theValue” to 10 so that it can be used in the next select statement.

DECLARE @TheValue nvarchar(50)

SELECT @TheValue = deviceManager.SystemSettings.Value 
FROM deviceManager.SystemSettings 
WHERE (deviceManager.SystemSettings.Setting = 'expire-terminal-requests'

What would be the best solution?

Answer 1

@TheValue will be NULL if the select doesn't hit any rows. And NULL is a good value to indicate "not found".

One trick with NULLs is that you have to check for them with is null instead of = null, for example:

where @TheValue is NULL

Answer 2

DECLARE @TheValue nvarchar(50)

SELECT @TheValue = deviceManager.SystemSettings.Value 
FROM deviceManager.SystemSettings 
WHERE (deviceManager.SystemSettings.Setting = 'expire-terminal-requests'

-- Assuming @TheValue is an output parameter
SELECT @TheValue = ISNULL(@TheValue, 10)

Answer 3

coalesce returns the first non-null value from the list, is also ANSI standard.

SET @TheValue = coalesce (some_expresson_that_may_return_null
                         ,some_other_expresson_that_may_return_null
                         ,and_another_expresson_that_may_return_null
                         ,default_value)

Answer 4

Another possibility, set the default value before the query

DECLARE @TheValue nvarchar(50)

SET @TheValue = 'Some default Value'

SELECT @TheValue = deviceManager.SystemSettings.Value FROM deviceManager.SystemSettings WHERE deviceManager.SystemSettings.Setting = 'expire-terminal-requests'

This will always return either the default or the correct value.

Hope this helps.