The drug was used in the film to keep the kids from dreaming, and therefore, keeping Freddy Krueger at bay.
The side effects included addiction, hallucinations, high blood pressure, and stroke or death.
The goal of the kids in Freddy vs. Jason was to use the drug to keep them from dreaming. The kids found a hospital that was using Hypnocil. While at the hospital, one of the kids separated from the group to go get stoned. During his smoking, he fell asleep, and Freddy greeted him as a caterpillar-like creature who forced his way into the stoner's mouth, thereby possessing him. In the real world, the possessed stoner found the cabinet with the Hypnocil, locked the door to the room, and began dumping the Hypnocil down the drain. The other kids in the group try to stop him, but are unsuccessful.