为存储的proc select语句设置默认值
•浏览 1
Setting a default value for a stored proc select statement
我正在创建一个存储过程，它从表中选择一个值并在另一个过程中使用它。如果搜索到的第一个值不存在，我需要它使用默认值。我是存储过程的新手，所以我不确定最佳实践。
这是第一个可能返回值也可能不返回值的选择语句。如果它没有返回值，我需要将"@theValue"设置为 10，以便它可以在下一个 select 语句中使用。
DECLARE @TheValue nvarchar(50)
SELECT @TheValue = deviceManager.SystemSettings.Value
FROM deviceManager.SystemSettings
WHERE (deviceManager.SystemSettings.Setting = 'expire-terminal-requests'
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)
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)
WHERE @TheValue IS NULL最好的解决方案是什么？
DECLARE @TheValue nvarchar(50)
SELECT @TheValue = deviceManager.SystemSettings.Value
FROM deviceManager.SystemSettings
WHERE (deviceManager.SystemSettings.Setting = 'expire-terminal-requests'
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)
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)
WHERE @TheValue IS NULL另一种可能，在查询前设置默认值
声明@TheValue nvarchar(50)
SET @TheValue = \\'一些默认值\\'
SELECT @TheValue = deviceManager.SystemSettings.Value
FROM deviceManager.SystemSettings
WHERE deviceManager.SystemSettings.Setting = \\'expire-terminal-requests\\'
这将始终返回默认值或正确值。
希望这会有所帮助。
coalesce 返回列表中的第一个非空值，也是 ANSI 标准。
DECLARE @TheValue nvarchar(50)
SELECT @TheValue = deviceManager.SystemSettings.Value
FROM deviceManager.SystemSettings
WHERE (deviceManager.SystemSettings.Setting = 'expire-terminal-requests'
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)
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)
WHERE @TheValue IS NULL如果选择没有命中任何行，
@TheValue 将为 NULL。 NULL 是一个很好的值来表示"未找到"。
使用 NULL 的一个技巧是您必须使用 is null 而不是 = null 来检查它们，例如：
DECLARE @TheValue nvarchar(50)
SELECT @TheValue = deviceManager.SystemSettings.Value
FROM deviceManager.SystemSettings
WHERE (deviceManager.SystemSettings.Setting = 'expire-terminal-requests'
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)
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)
WHERE @TheValue IS NULL