Silent Hill 1, 2 (especially 2) and 4 are extremely scary (I didn't find 3 that scary). RE1 (the first game) was quite atmospheric but all other RE games since are not even close to Silent Hill series. Project Zero series were nice too.

Oh and Vagrant Story. It was thrilling to play but not scary. It's probably one of Squaresoft's best product next to FFTactics, and Xenogears.