Rudy, I had to add a ')' before the ORDER BY in order to close off the 2 '(' located after the IN statement.
I was spending a lot of time trying to get this working on my site when I thought (wait, I can just run a test exec in the enterprise manager!)
So I ran your code on the Testimonials table (it has 2 records, ids: 1 and 3). I ran:
exec nameofsp 'testimonial', 1, 'next'
And it returned record 3, good so far. Then I ran
exec nameofsp 'testimonial', 3, 'next'
AND IT RETURNED record 1! WOOHOO! Just to be safe, I then ran:
exec nameofsp 'testimonial', 1, 'prev'
And up came record 3!
I think my only possible problem comes when someone tries to navigate when only 1 record exists. Rather than involve more SQL, I'll just have the application language look at a record count variable that I put in tandem with your code, and if it's set to 1, then disable the navigation so that the stored proc is not called.
Excellent code, Rudy!