Query Sql Server able to delete the last row of the table

If @Prov is the Max(key) of the table, your query

DELETE FROM Prov_inser WHERE (SELECT MAX (Province_Inserite) FROM Prov_inser) = @Prov

is equal to

DELETE FROM Prov_inser WHERE 1 = 1

which will delete all rows in the table

===================================================================

So you have to change it to

DELETE FROM Prov_inser WHERE Province_Inserite = @Prov

or without parameter

DELETE FROM Prov_inser WHERE Province_Inserite = (SELECT MAX(Province_Inserite) FROM Prov_inser)

If @Prov isn't a key, you have to add a key or a timestamp audit field (i.g. Created_Time) to identify the last inserted row of the table.