Store procedure timing out sometimes
It's impossible to say what the problem is without knowing how the data is structured, how many rows are in each table, what the indexes are, and other information. Even then, the problem may not even be with this query but with some other query that causes problems for the database as a whole. Basically, your question is far too narrow. 

That said, you could try a few things:


Try running this with Explain Plan turned on. That should tell you what part of the query is taking the longest.
Run SET STATISTICS IO ON; and then your query. That will give you an idea if the I/O usage is high. 
If the above don't suggest improvement, check the overall DB for problems. Glenn Berry's SQL diagnostics queries are a good place to start. 
If you still can't figure it out, hire someone like Glenn to help you. Or start reading a lot of books on SQL Server optimization.


(disclaimer: Glenn's a friend and former colleague of mine, but he is really good at this stuff. I don't get kickbacks. Maybe a beer, but probably not.)