Hi
If you are working with sqlserver database then debugging process is very simple as compare to other database.
If you are working with sqlserver 2005 then you can debug the store procedure like
Sqlserver 2005 debugging process
If you are working with sqlserver 2008 then you can debug the store procedure like
Sqlserver 2008 debugging process
I hope it will help to someone.
Chandradev's Blog 