所有时间显示NULL值请帮帮我... [英] All time show NULL value please help me...
问题描述
所有时间显示空值
请帮我解释我的代码有什么问题
我尝试过:
All Time Show Null value
Please help me what is the wrong in my code
What I have tried:
declare @mxdbvlu int
declare @dbvlunm int
set @mxdbvlu= (select max(Database_name) from TBL_FY_SELECT_1)
if @mxdbvlu = null
set @dbvlunm=1000
else
set @dbvlunm=sum(@mxdbvlu+1)
select @dbvlunm,@mxdbvlu
推荐答案
如果我创建一个包含两列的表:ID(int,Identity)和Database_name(NVARCHAR,MAX)和运行你的代码,我确实得到NULL,NULL。
但是添加两个数据行:
If I create a table with two columns: ID (int, Identity) and Database_name (NVARCHAR, MAX) and run your code, I do indeed get NULL, NULL.
But add two data rows:
ID Database_name
1 AAAA
2 AAAB
我没有 - 我得到一个例外,因为它不能将AAAA转换为整数值。
如果我用这个替换数据:
And I don't - I get an exception becuase it can't convert "AAAA" to an integer value.
If I replace the data with this:
ID Database_name
1 1111
2 1112
然后我得到合理数据:
Then I get "reasonable" data:
(No column name) (No column name)
1113 1112
所以你选择的专栏名称显然是错的:你不能在那里加上名字。
我只能在没有行的情况下复制你的失败 - 所以先检查一下你的数据。
但是......如果你用它来构建一个新的每次添加一些数据时都带有顺序名称的数据库,那么这可能是一个糟糕的设计。我强烈建议你重新考虑为什么你认为这将是一个很好的解决方案 - 来自为什么我没有使用IDENTITY列? POV,以及为什么我需要新的数据库,这只是愚蠢的方法。
So your choice of column name is clearly wrong: you can't put a name in there.
And I can only replicate your failure when there are no rows - so start by checking your data.
But ... if you are using this to build a new database with sequential names each time you add some data, then that's probably a bad design. I'd strongly suggest that you rethink exactly why you decided this would be a good solution - both from a "why didn't I use an IDENTITY column?" POV, and a "why the heck do I need new databases at all, that's just silly" approach.
我可以看到你的代码有两个明显的问题:
I can see two obvious issues with your code:
if @mxdbvlu = null
如果其中一个或两个操作数都是 NULL
,则比较运算符( =
,!=
)将返回 UNKNOWN
。这既不是真也不是假,所以 If
分支将不会执行。
测试是否值是 NULL
,请使用 Is Null
:
IS NULL(Transact-SQL) - SQL Server Microsoft Docs [ ^ ]
SQL和三值逻辑的陷阱 - 简单的谈话 [ ^ ]
If either or both operands are NULL
, the comparison operators (=
, !=
) will return UNKNOWN
. This is neither true nor false, so the If
branch will not be executed.
To test whether a value is NULL
, use Is Null
instead:
IS NULL (Transact-SQL) - SQL Server | Microsoft Docs[^]
SQL and the Snare of Three-Valued Logic - Simple Talk[^]
set @dbvlunm=sum(@mxdbvlu+1)
由于您没有计算一组记录的聚合,因此这里不需要 Sum(...)
。只需使用:
Since you're not calculating an aggregate over a set of records, you don't need Sum(...)
here. Just use:
set @dbvlunm = @mxdbvlu + 1
这篇关于所有时间显示NULL值请帮帮我...的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!