How to print loging/Debug a stored procedure - TSQL - SQL Server

You can use print and raiserror statments to print messages on console window in Managment studio

ALTER          PROC [dbo].[p_my_debug_proc]
@Dt as varchar(12),
@EDt as varchar(12),
@ManualRun as Integer
AS


print @Dt
print @EDt
print @ManualRun
print 'before raiserror'
raiserror('this is a raised error', 18, 1)
 print 'before return'
if (@ManualRun = 1)
       begin
                print 'in If block';
--print DATEADD(d,0,@Dt);
print DATEADD(d,0,CONVERT(datetime,@Dt,105));

end
else
print 'else block'
return 
print 'after return'


--------------------------


RAISERROR ('This is the error message,,,,', 0, 1) WITH NOWAIT;