Try the below sample
DECLARE @Inserted_Identy Bigint;
if not exists(Select * From TableA
Where NAME = @NAME and PINCODE = @Pincode )
Begin
Insert into TableA (NAME,PINCODE,Address,MOB)
Values(@NAME,@Pincode,@Address,@MOB)
Set @Inserted_Identy = (SELECT SCOPE_IDENTITY())
End
ELSE
BEGIN
SELECT @Inserted_Identy = Id from TableA
Where NAME = @NAME and PINCODE = @Pincode
END
Update TableB Set TABLEA_ID = @Inserted_Identy
Where ROOM_ID = @ROOM_ID and BILLNO = @BILLNO