How to check if value is inserted successfully or not?

by

You can use @@ROWCOUNT server variable immediately after the insert query to check number of affected rows by the insert operation. 

declare @fName varchar(50) = 'Abcd',
        @lName varchar(50) = 'Efgh'
INSERT INTO myTbl(fName,lName) values(@fName,@lName)

PRINT @@ROWCOUNT --> 0- means no rows affected/nothing inserted 
                 --> 1- means your row has been inserted successfully

For your requirement, you could use a Case statement(as per comment):

--If you need @check as a bit type please change Int to bit
DECLARE @check Int = CASE WHEN @@ROWCOUNT = 0 THEN 1 ELSE 0 END

Leave a Comment