Only with SQL 2008 and SSMS 2008. Select from menu ‘Debug\Start Debugging’ or press Alt+F5 and will launch the step by step T-SQL debugger.
On 2005 the only way is to attach Profiler and monitor for the SP:StmtCompleted event, which is not exactly debugger step-by-step, but at least you’ll see the execution flow. Not to be done on a production machine, obviously.