sql - Cannot insert NULL values into column 'USERNAME', table 'tempdb.dbo.#temptable error -
i have sp. while executing getting error as
cannot insert value null column 'username', table 'tempdb.dbo.#temptable__________________________________________________________________________________________________________0000000002fd'; column not allow nulls. update fails. statement has been terminated.
below sp
alter procedure [dbo].[userreportdata] @as_ondate datetime begin declare @reportdate datetime declare @username varchar(110) select * #temptable ( select a.cuser_id, b.user_id, a.u_datetime reportdate, b.first_name + ' ' + b.last_name username inward_doc_tracking_trl inner join user_mst b on a.cuser_id = b.mkey , a.u_datetime >= @as_ondate ) x declare cur_1 cursor select cuser_id, user_id #temptable open cur_1 declare @cuser_id int declare @user_id int fetch next cur_1 @cuser_id, @user_id while (@@fetch_status = 0) begin select @reportdate = u_datetime inward_doc_tracking_trl u_datetime >= @as_ondate update #temptable set reportdate = @reportdate, username = @username cuser_id = @cuser_id , user_id = @user_id fetch next cur_1 @cuser_id, @user_id end close cur_1 deallocate cur_1 select * #temptable drop table #temptable end updated procedure
alter procedure [dbo].[userreportdata] @as_ondate datetime begin declare @reportdate datetime declare @username varchar(110) select * #temptable ( select a.cuser_id, b.user_id, a.u_datetime reportdate, isnull(b.first_name, '') + ' ' + isnull(b.last_name, '') username inward_doc_tracking_trl inner join user_mst b on a.cuser_id = b.mkey , a.u_datetime >= @as_ondate ) x declare cur_1 cursor select cuser_id, user_id #temptable open cur_1 declare @cuser_id int declare @user_id int --declare @username varchar (100) fetch next cur_1 @cuser_id, @user_id while (@@fetch_status = 0) begin select @reportdate = u_datetime inward_doc_tracking_trl u_datetime >= @as_ondate select @username = isnull(b.first_name, '') + ' ' + isnull(b.last_name, '') inward_doc_tracking_trl inner join user_mst b on a.cuser_id = b.mkey a.u_datetime >= @as_ondate update #temptable set reportdate = @reportdate, username = @username cuser_id = @cuser_id , user_id = @user_id fetch next cur_1 @cuser_id, @user_id end close cur_1 deallocate cur_1 select * #temptable drop table #temptable end
first comment @username declaration in initial begin block
-- declare @username varchar(110) then handle null value username in select * into block
isnull(b.first_name, '') + ' ' + isnull(b.last_name, '') username then add @username in cursor as
declare cur_1 cursor select cuser_id, user_id, username #temptable open cur_1 declare @cuser_id int declare @user_id int declare @username varchar (200) -- can set required length fetch next cur_1 @cuser_id, @user_id then @username won't throw null error
update: based on comments, updated answer:
so want @username based on u_datetime >= @as_ondate condition.
so use existing code , add 1 more block inside cursor getting @username value after select @reportdate = u_datetime inward_doc_tracking_trl u_datetime >= @as_ondate
select @username = isnull(b.first_name, '') + ' ' + isnull(b.last_name, '') -- no need of column alias here inward_doc_tracking_trl inner join user_mst b on a.cuser_id = b.mkey a.u_datetime >= @as_ondate
Comments
Post a Comment