Print output of a dynamic query?

Question

I am able to kinda get the output of a table being used in my stored procedure using the following way:-

DECLARE @xmltmp xml = (SELECT * FROM table FOR XML AUTO)
PRINT CONVERT(NVARCHAR(MAX), @xmltmp)

Now, I came across a sp which makes use of dynamic query inside it.

SELECT @query = 'SELECT * FROM ' + @TempTableVariable;
EXECUTE sp_executesql @query

I want to do a similar output print (like xml print above) with the query.

I looked around and I see getting output in one variable only

Getting result of dynamic SQL into a variable for sql-server

Dynamic SQL output of a query to a variable

I don't understand what you're asking here. What is `@TempTableVariable`? A variable is not a temporary object, and a temporary object is not a variable. If it's a table type variable, the above will error, and if it's a scalar variable, the above look like it's open to injection attacks. — Thom A, Mar 02 '21 at 14:40
If you want the output from your sp_executesql into a temp table/staging table, you can do that, look up SP execute results insert into table. — Brad, Mar 02 '21 at 14:41
@Larnu it is a nvarchar ```DECLARE @TempTableVariable nvarchar(300) = '[##TempTable_'+ convert(nvarchar(200),newid()) + ']'``` — Shad, Mar 02 '21 at 14:42
SQL is not designed to generate "prettty" output. What you see when you examine a resultset is a function of whatever application is consuming that resultset and generating output for visual use. Use a tool that suits your goal - perhaps even MS Office tools. — SMor, Mar 02 '21 at 16:13

score 0 · Answer 1 · answered Mar 02 '21 at 15:09

I think this is mostly a question about formatting the data to look more like a table, since you are able to get the table data as you indicated in your question. I used a sample table with the following structure and data:

create table Sample
(   Column1 date
,   Column2 char(3)
,   Column3 decimal(5, 2)
,   Column4 varchar(5)
)

insert
    dbo.Sample
(   Column1
,   Column2
,   Column3
,   Column4
)
values
  ('2020-09-28', 'AC1', 7.5, 'Mon')
, ('2021-02-15', 'AC2', 7.5, 'Wed')
, ('2021-02-16', 'AC2', 7.5, 'Thurs')
, ('2021-02-10', 'AC3', 7.5, 'Mon')
, ('2021-02-12', 'AC4', 3.5, 'Tue')
, ('2021-02-08', 'AC4', 1,   'Mon')
, ('2021-02-10', 'AD4', 7.5, 'Wed')
, ('2021-02-11', 'AD4', 7.5, 'Thurs')
, ('2021-02-15', 'AD4', 7.5, 'Mon')
, ('2021-02-16', 'AD4', 7.5, 'Tue')
, ('2021-02-17', 'AD4', 7.5, 'Wed')
, ('2021-02-18', 'AD4', 7.5, 'Thurs')
, ('2021-02-22', 'AC4', 7.5, 'Mon')

One approach might be something like:

declare @tablerows varchar(max) = ''

select
    @tablerows = @tablerows + '''' + convert(varchar, s.Column1) + ''', ''' + s.Column2 + ''', ' + convert(varchar, s.Column3) + ', ''' + s.Column4 + '''
'
from
    dbo.Sample s


print @tablerows

which gives the results:

'2020-09-28', 'AC1', 7.50, 'Mon'
'2021-02-15', 'AC2', 7.50, 'Wed'
'2021-02-16', 'AC2', 7.50, 'Thurs'
'2021-02-10', 'AC3', 7.50, 'Mon'
'2021-02-12', 'AC4', 3.50, 'Tue'
'2021-02-08', 'AC4', 1.00, 'Mon'
'2021-02-10', 'AD4', 7.50, 'Wed'
'2021-02-11', 'AD4', 7.50, 'Thurs'
'2021-02-15', 'AD4', 7.50, 'Mon'
'2021-02-16', 'AD4', 7.50, 'Tue'
'2021-02-17', 'AD4', 7.50, 'Wed'
'2021-02-18', 'AD4', 7.50, 'Thurs'
'2021-02-22', 'AC4', 7.50, 'Mon'

Another approach might be something like this:

declare @tablerows varchar(max) = convert(varchar(max), (SELECT * FROM dbo.Sample rows FOR XML AUTO))

set @tablerows = replace(@tablerows, '/><rows', '
')
set @tablerows = replace(@tablerows, '<rows', '')
set @tablerows = replace(@tablerows, '/>', '')

print @tablerows

which gives results like:

 Column1="2020-09-28" Column2="AC1" Column3="7.50" Column4="Mon"
 Column1="2021-02-15" Column2="AC2" Column3="7.50" Column4="Wed"
 Column1="2021-02-16" Column2="AC2" Column3="7.50" Column4="Thurs"
 Column1="2021-02-10" Column2="AC3" Column3="7.50" Column4="Mon"
 Column1="2021-02-12" Column2="AC4" Column3="3.50" Column4="Tue"
 Column1="2021-02-08" Column2="AC4" Column3="1.00" Column4="Mon"
 Column1="2021-02-10" Column2="AD4" Column3="7.50" Column4="Wed"
 Column1="2021-02-11" Column2="AD4" Column3="7.50" Column4="Thurs"
 Column1="2021-02-15" Column2="AD4" Column3="7.50" Column4="Mon"
 Column1="2021-02-16" Column2="AD4" Column3="7.50" Column4="Tue"
 Column1="2021-02-17" Column2="AD4" Column3="7.50" Column4="Wed"
 Column1="2021-02-18" Column2="AD4" Column3="7.50" Column4="Thurs"
 Column1="2021-02-22" Column2="AC4" Column3="7.50" Column4="Mon"

Of course neither of these is exactly the same as selecting from table and outputting to text, but you could build from either of these approaches to get there depending on how much you care about the formatting.

Print output of a dynamic query?

1 Answers1