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

Posted by Karthik2010_Mca under Sql Server on 8/4/2013 | Points: 10 | Views : 1482 | Status : [Member] | Replies : 3
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


Responses

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

Up
0
Down

Resolved
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

Up
0
Down
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

Up
0
Down
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

Login to post response