SQL select print out results of stored procedure

My businesses application supports only reporting with selected data from SQL server.In one business process I have very complicated stored procedure which usi开发者_运维技巧ng others stored procs and it was designed to print out results as log of job done. What I want to catch that print out and select it as varchar(max) so my app can handle that data and display to user.

Here is sample scenario described in TSQL code:

create procedure sp_test_print_out
as
begin
    Print 'Test';
    print 'Test 1';
end
go

create procedure sp_test_print_out_to_select
as 
declare @printOut varchar(max)
set @printOut = exec sp_test_print_out --How I can achieve this ?
select @printOut
end

go

exec sp_test_print_out_to_select

---

You can try setting the values in output parameter

create procedure sp_test_print_out
@printMessages varchar(max) output
as
begin
set @printMessages='Test'
Print 'Test';

set @printMessages= @printMessages + CHAR(10)
set @printMessages= @printMessages + 'Test 1'
print 'Test 1';
end
go


create procedure sp_test_print_out_to_select
as 
begin
declare @printOut varchar(max)
exec sp_test_print_out @printOut output -- can be achieved using output parameter ?
select @printOut
end

go

exec sp_test_print_out_to_select

---

There is also one rough and probably BAD way to get selected data from print commands inside stored procedure.

Command xp_cmdshell and sqlcmd can do the JOB. Xp_cmdshell is mostly disabled and not allowed to use at most of SQL servers because of security reasons.

Here is code:

CREATE TABLE #temp
(OUTPUT VARCHAR(MAX));

    declare @cmd varchar(800);
    set  @cmd = 'sqlcmd -d RobotTest -Q "exec sp_test_print_out"';

    INSERT INTO #TEMP
    exec xp_cmdshell @cmd  ;

select output from #temp;
drop table #temp;