Thanks All, The solutions are working fine. Thanks for your inputs.

Do we have an option incase if we are not scheduling them in SQL job ? I mean if we execute a stored procedure normally in SSMS, can we get those messages and store them in a table ??