Is it possible for another process to insert one or more rows into SMS_Submit while this stored procedure is running? If so, that would explain why some rows are not deleted. It would also mean that you will have some rows that get deleted before they are inserted into SMS_Bill which means you will lose data.
To ensure that only those rows that have been inserted into SMS_Bill are deleted from SMS_Submit and that if there is a problem the database will remain consistent, you should do the following:
1. Add a column named ToBeTransferred to SMS_Submit table
2. Use this code to do the transfer of rows from SMS_Submit to SMS_Bill
BEGIN TRANSACTION MOVESMSROWS WITH MARK;
Update SMS_Submit Set ToBeTransferred=1 Where id=@id;
Insert into SMS_Bill (UserNumber, MessageCoding, MessageContent)
Select UserNumber, MessageCoding, MessageContent from SMS_Submit
Where id = @id AND ToBeTransferred=1;
Delete from SMS_Submit where id=@id AND ToBeTransferred=1;
COMMIT TRANSACTION MOVESMSROWS;
See
BEGIN TRANSACTION (Transact-SQL)[
^]