How to avoid null value during sql insertion. [Resolved]

Posted by Karthik2010_Mca under Sql Server on 8/4/2013 | Points: 10 | Views : 1845 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi All,

In the below example, I want to insert all records. But during insertion C_Is_LumpSum field is taken empty.
I need to avoid empty record in the column C_Is_LumpSum.

Note : This table already having data with the column C_Is_LumpSum having some empty field. Hereafter I want to insert value in the column C_Is_LumpSum.

Ex:
IF EXISTS (select I_Student_ID, I_Batch_ID, C_Is_LumpSum from dbo.T_Student_Batch_Details where C_Is_LumpSum != '')
BEGIN
INSERT INTO dbo.T_Student_Batch_Details (
I_Student_ID,
I_Batch_ID,
I_Status,
Dt_Valid_From,
C_Is_LumpSum
) VALUES (
142586,
5041,
1,
GETDATE(),
'N')
END

Thanks

Karthik

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Sekar.C on: 8/5/2013 [Member] Starter | Points: 50

0	Hi, Try this one. IF EXISTS (select I_Student_ID, I_Batch_ID, C_Is_LumpSum from dbo.T_Student_Batch_Details where C_Is_LumpSum != '') BEGIN IF(ISNULL(@C_Is_LumpSum,'')<>'')//Before insert values to table check not null for @C_Is_LumpSum BEGIN INSERT INTO dbo.T_Student_Batch_Details ( I_Student_ID, I_Batch_ID, I_Status, Dt_Valid_From, C_Is_LumpSum ) VALUES ( 142586, 5041, 1, GETDATE(), 'N') END END Regards Sekar.c Karthik2010_Mca, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,
Try this one.

IF EXISTS (select I_Student_ID, I_Batch_ID, C_Is_LumpSum from dbo.T_Student_Batch_Details where C_Is_LumpSum != '')
BEGIN
IF(ISNULL(@C_Is_LumpSum,'')<>'')//Before insert values to table check not null for @C_Is_LumpSum
BEGIN
INSERT INTO dbo.T_Student_Batch_Details (
I_Student_ID,
I_Batch_ID,
I_Status,
Dt_Valid_From,
C_Is_LumpSum
) VALUES (
142586,
5041,
1,
GETDATE(),
'N')
END
END

Regards
Sekar.c

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/5/2013 [Member] [MVP] Platinum | Points: 25

0	means you want to replace that empty value with some value ( i.e. 'N'/'Y' ) or the upcoming records must have value ? Case1: If you want to replace empty value with some value, use UPDATE statement as follows: UPDATE dbo.T_Student_Batch_Details SET C_Is_LumpSum = 'N' WHERE C_Is_LumpSum IS NULL OR C_Is_LumpSum = '' Case2: IF EXISTS (select I_Student_ID, I_Batch_ID, C_Is_LumpSum from dbo.T_Student_Batch_Details where C_Is_LumpSum != '' OR C_Is_LumpSum IS NOT NULL ) BEGIN INSERT INTO dbo.T_Student_Batch_Details ( I_Student_ID, I_Batch_ID, I_Status, Dt_Valid_From, C_Is_LumpSum ) VALUES ( 142586, 5041, 1, GETDATE(), 'N') END Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Karthik2010_Mca, if this helps please login to Mark As Answer. \| Alert Moderator

means you want to replace that empty value with some value ( i.e. 'N'/'Y' ) or the upcoming records must have value ?
Case1:
If you want to replace empty value with some value, use UPDATE statement as follows:

UPDATE dbo.T_Student_Batch_Details 

SET C_Is_LumpSum = 'N'

WHERE C_Is_LumpSum IS NULL OR C_Is_LumpSum = ''

Case2:

IF EXISTS (select I_Student_ID, I_Batch_ID, C_Is_LumpSum from dbo.T_Student_Batch_Details where C_Is_LumpSum != '' OR C_Is_LumpSum IS NOT NULL ) 

BEGIN 

	INSERT INTO dbo.T_Student_Batch_Details ( 

	I_Student_ID, 

	I_Batch_ID, 

	I_Status, 

	Dt_Valid_From, 

	C_Is_LumpSum 

	) VALUES ( 

	142586, 

	5041, 

	1, 

	GETDATE(), 

	'N') 

END

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/6/2013 [Member] [MVP] Platinum | Points: 25

0	hi karthik, You can use COALESCE() scalar function to check for Null or empty string ... IF ( COALESCE( @C_Is_LumpSum, '') <> '') --insert the values Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Karthik2010_Mca, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts