How to check if value is inserted successfully or not?

Question

I have a procedure where I insert values into my table.

declare @fName varchar(50),@lName varchar(50),@check tinyint
INSERT INTO myTbl(fName,lName) values(@fName,@lName)

EDITED:

Now I want check if it inserted successfully set @check = 0 else @check = 1

Answer 1

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

Answer 2

You need to use @@ROWCOUNT

It returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.

@@ROWCOUNT is both scope and connection safe.

In fact, it reads only the last statement row count for that connection and scope.

It’s safe to use @@ROWCOUNT in SQL Server even when there is a trigger on the base table. The trigger will not skew your results; you’ll get what you expect. @@ROWCOUNT works correctly even when NOCOUNT is set.

so you query should be:

declare @fName varchar(50), @lName varchar(50), @check tinyint = 0
...
INSERT INTO myTbl(fName,lName) values(@fName,@lName)
if  @@ROWCOUNT>0   
  set @check = 1

Answer 3

You can use @@rowcount after insert table, like this:

DECLARE @check int

INSERT INTO Employees (Name,Email,Phone,[Address])
VALUES('Test','[email protected]','','')

if(@@ROWCOUNT>0)
SET @check=1

SELECT @check;