Old is Gold
--------------
By using the Print() command of TSQL ( https://msdn.microsoft.com/en-us/library/ms176047.aspx )
Since Sql Server 2008
------------------------
From SSMS,we can start the debugger by either clicking the Debug button on the Query toolbar or by clicking Start Debugging on the Debug menu or pressing ALT+F5.
From Visual Studio 2010 (Extra information, though not asked)
---------------------------------------------------------------------
Step 1: Open Server Explorer -> Server nodes-> SQL Server machine name -> SQL Servers node -> SQL Server instance -> Your database node -> stored procedures node.
Step 2: Right-click YOUR stored procedure and then click Step Into Stored Procedure.
Step 3: Press F11 to step through the stored procedure.
Hope this helps
--
Thanks & Regards,
RNA Team
Chamu, if this helps please login to Mark As Answer. | Alert Moderator