sqldba2013
asked on
How to allow NULL values in SQL query
Hi,
I am using ISNULL function to allow NULL values in Insert statement but still I am getting below error. Please suggest how to fix below error or please tell me how to allow null values in SQL query.
Error:
Cannot insert the value NULL into column 'O_ID', table 'dbo.P_Tbl'; column does not allow nulls. INSERT fails.
Query
I am using ISNULL function to allow NULL values in Insert statement but still I am getting below error. Please suggest how to fix below error or please tell me how to allow null values in SQL query.
Error:
Cannot insert the value NULL into column 'O_ID', table 'dbo.P_Tbl'; column does not allow nulls. INSERT fails.
Query
IF object_id('tempdb..#temp') IS NOT NULL
BEGIN
DROP TABLE #temp
END
create table #temp(ID INT IDENTITY(1, 1) ,ActID bigint)
DECLARE @v_ActID bigint;
DECLARE @v_Flag int;
DECLARE @v_UsrID bigint;
DECLARE @v_userid bigint;
SET @v_Flag=1;
SET @v_UsrID = 0;
SET @v_userid = 0;
SELECT @v_ActID=ActID from #temp WITH(NOLOCK) where id=@v_Flag
SET @v_UsrID = 0;
SELECT @v_UsrID=O_ID FROM P_Tbl WITH(NOLOCK) WHERE O_ID=@v_ActID AND UsrID=@v_userid
SET @v_UsrID=isnull(@v_UsrID,0);
IF (@v_UsrID=0)
BEGIN
INSERT INTO P_Tbl (O_ID,UsrID,C_By,M_By,C_Date,ISR)
VALUES (@v_ActID,@v_userid,-1,-1,getdate(),1)
END
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I see you are trying to get a value from #temp without inserting anything into #temp so that column will return a null..
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your suggestion.
I have modified query with above changes and still I am getting same error.
I have modified query with above changes and still I am getting same error.
IF object_id('tempdb..#temp') IS NOT NULL
BEGIN
DROP TABLE #temp
END
create table #temp(ID INT IDENTITY(1, 1) ,ActID bigint)
DECLARE @v_ActID bigint;
DECLARE @v_Flag int;
DECLARE @v_UsrID bigint;
DECLARE @v_userid bigint;
SET @v_Flag=1;
SET @v_UsrID = 0;
SET @v_userid = 0;
SET @v_ActID=isnull(@v_ActID,0);
SELECT @v_ActID=ActID from #temp WITH(NOLOCK) where id=@v_Flag
SET @v_UsrID = 0;
SELECT @v_UsrID=O_ID FROM P_Tbl WITH(NOLOCK) WHERE O_ID=@v_ActID AND UsrID=@v_userid
SET @v_UsrID=isnull(@v_UsrID,0);
IF (@v_UsrID=0)
BEGIN
INSERT INTO P_Tbl (O_ID,UsrID,C_By,M_By,C_Date,ISR)
VALUES (COALESCE(@v_ActID,0),@v_userid,-1,-1,getdate(),1)
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to all for your inputs and my issue has been resolved.