无法将 NULL 值插入列 'USERNAME'、表 'tempdb.dbo.#temptable 错误 [英] Cannot insert NULL values into column 'USERNAME', table 'tempdb.dbo.#temptable error
问题描述
我有一个 SP.执行时出现错误
I have a SP. while executing I am getting error as
无法将值 NULL 插入到列 'USERNAME'、表 'tempdb.dbo.#temptable____________________________________________________________________________________________________0000000002FD';列不允许空值.更新失败.声明已终止.
Cannot insert the value NULL into column 'USERNAME', table 'tempdb.dbo.#temptable__________________________________________________________________________________________________________0000000002FD'; column does not allow nulls. UPDATE fails. The statement has been terminated.
下面是我的SP
ALTER PROCEDURE [dbo].[UserReportData]
@As_ONDATE Datetime
AS
BEGIN
DECLARE @REPORTDATE datetime
DECLARE @USERNAME varchar(110)
Select * INTO #temptable
FROM
(
select a.CUser_id, b.User_Id, a.U_datetime
as REPORTDATE, b.first_Name + ' ' + b.last_name as USERNAME
from inward_doc_tracking_trl a inner join user_mst b
on a.CUser_id = b.mkey
and a.U_datetime >= @As_ONDATE
) as x
DECLARE Cur_1 CURSOR
FOR SELECT CUser_id, User_Id FROM #temptable
OPEN Cur_1
DECLARE @CUser_id INT
DECLARE @User_Id INT
FETCH NEXT FROM Cur_1
INTO @CUser_id, @User_Id
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @REPORTDATE = U_datetime FROM inward_doc_tracking_trl
where U_datetime >= @As_ONDATE
UPDATE #temptable
SET REPORTDATE = @REPORTDATE,
USERNAME = @USERNAME
WHERE CUser_id = @CUser_id
AND User_Id = @User_Id
FETCH NEXT FROM Cur_1 INTO @CUser_id, @User_Id
END
CLOSE Cur_1
DEALLOCATE Cur_1
SELECT * FROM #temptable
DROP TABLE #temptable
END
更新程序
ALTER PROCEDURE [dbo].[UserReportData]
@As_ONDATE Datetime
AS
BEGIN
DECLARE @REPORTDATE datetime
DECLARE @USERNAME varchar(110)
Select * INTO #temptable
FROM
(
select a.CUser_id, b.User_Id, a.U_datetime
as REPORTDATE, ISNULL(b.first_Name, '') + ' ' + ISNULL(b.last_name, '') AS USERNAME
from inward_doc_tracking_trl a inner join user_mst b
on a.CUser_id = b.mkey
and a.U_datetime >= @As_ONDATE
) as x
DECLARE Cur_1 CURSOR
FOR SELECT CUser_id, User_Id FROM #temptable
OPEN Cur_1
DECLARE @CUser_id INT
DECLARE @User_Id INT
--DECLARE @USERNAME VARCHAR (100)
FETCH NEXT FROM Cur_1
INTO @CUser_id, @User_Id
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @REPORTDATE = U_datetime FROM inward_doc_tracking_trl
where U_datetime >= @As_ONDATE
SELECT @USERNAME = ISNULL(b.first_Name, '') + ' ' + ISNULL(b.last_name, '')
FROM inward_doc_tracking_trl a
INNER JOIN user_mst b on a.CUser_id = b.mkey
where a.U_datetime >= @As_ONDATE
UPDATE #temptable
SET REPORTDATE = @REPORTDATE,
USERNAME = @USERNAME
WHERE CUser_id = @CUser_id
AND User_Id = @User_Id
FETCH NEXT FROM Cur_1 INTO @CUser_id, @User_Id
END
CLOSE Cur_1
DEALLOCATE Cur_1
SELECT * FROM #temptable
DROP TABLE #temptable
END
推荐答案
首先注释初始 BEGIN 块中的@USERNAME 声明
First comment the @USERNAME declaration in the initial BEGIN block
-- DECLARE @USERNAME varchar(110)
然后像在 SELECT * INTO 块中一样处理 USERNAME 的 NULL 值
then handle the NULL value for the USERNAME as in the SELECT * INTO block
ISNULL(b.first_Name, '') + ' ' + ISNULL(b.last_name, '') AS USERNAME
然后在 CURSOR 中添加 @USERNAME 为
Then add the @USERNAME in the CURSOR as
DECLARE Cur_1 CURSOR
FOR SELECT CUser_id, User_Id, USERNAME FROM #temptable
OPEN Cur_1
DECLARE @CUser_id INT
DECLARE @User_Id INT
DECLARE @USERNAME VARCHAR (200) -- can set your required length
FETCH NEXT FROM Cur_1
INTO @CUser_id, @User_Id
那么 @USERNAME 就不会抛出 NULL 错误
Then the @USERNAME won't throw the NULL error
更新:根据您的评论，我更新了答案:
UPDATE: Based on your comments, I updated the answer:
所以您想根据 U_datetime >= @As_ONDATE 条件获取 @USERNAME.
So you want to get the @USERNAME based on the U_datetime >= @As_ONDATE condition.
因此，使用您现有的代码并在 CURSOR 中再添加一个块以获取 @USERNAME 值，就像 SELECT @REPORTDATE = U_datetime FROM inward_doc_tracking_trl where U_datetime >= @As_ONDATE
So use your existing code and add one more block inside the CURSOR for getting the @USERNAME value as after the SELECT @REPORTDATE = U_datetime FROM inward_doc_tracking_trl where U_datetime >= @As_ONDATE
SELECT @USERNAME = ISNULL(b.first_Name, '') + ' ' + ISNULL(b.last_name, '') -- no need of column alias here
FROM inward_doc_tracking_trl a
INNER JOIN user_mst b on a.CUser_id = b.mkey
WHERE a.U_datetime >= @As_ONDATE
这篇关于无法将 NULL 值插入列 'USERNAME'、表 'tempdb.dbo.#temptable 错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
