[SQL]The 'xxxx' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. | 亂馬客 - 點部落

2013-04-08

[SQL]The 'xxxx' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

3329
0
SQL

[SQL]The 'xxxx' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

今天看「SQL SERVER – The procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.」時，因為SP的回傳值不允許為Null，所以就傳回0。

那要如何解決這個問題呢?

作者的想法是加入ISNULL來判斷，如下，


AS
	DECLARE @MyFirstParam INT
	-- Additional Code
	RETURN (ISNULL(@MyFirstParam,0))
GO

DECLARE @R1 INT 
EXEC @R1 = TestSP
SELECT @R1

Dale的想法是使用COALESCE，COALESCE會傳回其引數中第一個非 Null 的運算式

2
AS
	DECLARE @MyFirstParam INT
	-- Additional Code
	RETURN (COALESCE(@MyFirstParam,0))
GO

DECLARE @R2 INT 
EXEC @R2 = TestSP2
SELECT @R2

亂馬客的想法是在定義回傳值的變數時，同時給初始值，(SQL 2008(含)以上)也是可以的，如下，

3
AS
	-- Additional Code
	DECLARE @MyFirstParam INT = 0
	RETURN @MyFirstParam
GO

DECLARE @R3 INT 
EXEC @R3 = TestSP3
SELECT @R3

Hi,

亂馬客Blog已移到了「亂馬客 : Re:從零開始的軟體開發生活」

請大家繼續支持 ^_^

回首頁