Yes, it should stop at 1.002 or 1.004 again. If you prime by adding sugar, you should end up with a SG of around 1.006 - 1.008. The remaining yeast will consume this sugar, converting roughly 50% into alcohol and 50% into CO2 and you will end up back somewhere around 1.002 to1.004. The "rule of thumb" is 2 teaspoons or about 10 grams per litre for "normal" carbonation of 2.0 - 2.5 volumes, which is about the carbonation level of most beer, soda, etc.
Depending on the rate of fermentation, if I miss out on bottling "on the way down", I can wait until fermentation has stopped then condition with sugar, juice, AJC or whatever up to my desired sweetness plus an allowance for carbonation. (e.g. if I want 1.008 sweeetness then I condition the now 1.002 cider up to 1.012 and plan to pasteurise when it has dropped back to 1.008 when it will be where I want it and carbonated).
The benefit (or otherwise) of adding sugar for carbonation is that you get extra alcohol. i.e. say 1.050 down to 1.002 gives you about 6.2% ABV plus 1.008 back down to 1.002 generates another 0.8% ABV.
Picking the right time to pasteurise can be a bit tricky. You can estimate it from the rate of fermentation (i.e. SG points drop per day, week or whatever), otherwise bottle some in a plastic soda bottle and use the squeeze test (compare it with a bottle of soda to get an idea of how much carbonation there is). I have rigged up a bottle (Grolsch is good for this) with a pressure gauge fitted to the cap. At normal room temperature, 1 volume of CO2 carbonation is around 15 psi or 1 Bar.