How to check if value is inserted successfully or not?
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
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
In SQL-Sever you can use OUTPUT clause to check if values are inserted successfully. By following query
declare @fName varchar(50),@lName varchar(50)
INSERT INTO myTbl(fName,lName) OUTPUT inserted.* values(@fName,@lName) ;
IF the values are inserted it will show output of inserted values. You can also store these values into new table.
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;