SQL select print out results of stored procedure

Question

My businesses application supports only reporting with selected data from SQL server.In one business process I have very complicated stored procedure which using 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

Thakur · Accepted Answer · 2010-09-30 09:44:06Z

1

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

answered Sep 30, 2010 at 9:44

Thakur

2,0306 gold badges24 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

adopilot Over a year ago

Thanx, That was also on my mind, I was hoop so that there is other easier way then handling output param. So I now need to handle all print out data, with new lines, error_messages and others

adopilot · Accepted Answer · 2010-09-30 17:42:21Z

0

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;

edited Sep 30, 2010 at 17:42

answered Sep 30, 2010 at 15:50

adopilot

4,58015 gold badges69 silver badges96 bronze badges

Collectives™ on Stack Overflow

SQL select print out results of stored procedure

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related