SQL Server PRINT SELECT (Print a select query result)?

Question

I am trying to print a selected value, is this possible?

Example:

PRINT 
    SELECT SUM(Amount) FROM Expense

The question asked about printing a value not about printing a table or a result set. In either case the language does not allow a sub query as an argument to the PRINT command. [Here is another SO question and answer](https://stackoverflow.com/a/5193984/3368958) that shows an example very similar to this one with reference to the PRINT documentation. — Nelda.techspiress, Nov 30 '16 at 19:26

Dan Field · Answer 1 · 2017-03-23T15:30:33.773

94

If you're OK with viewing it as XML:

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

While the OP's question as asked doesn't necessarily require this, it's useful if you got here looking to print multiple rows/columns (within reason).

edited Mar 23 '17 at 15:30

answered Apr 19 '16 at 21:19

Dan Field

20,885
5
55
71

3

This is _amazing_! There's so many cases where you'd want to use `PRINT` to dump out results and adding a custom proc for it is too much hard work. Great solution. – Rory Apr 24 '16 at 19:53
Yeah, I've used this in SSMS where adding a SELECT * FROM would cause problems for other applications/users. – Dan Field Apr 25 '16 at 00:05
3

On older versions of SQL Server you need DECLARE `@xmltmp xml; SELECT @xmltmp = (SELECT * FROM table FOR XML AUTO); PRINT CONVERT(NVARCHAR(MAX), @xmltmp)` – richard Mar 16 '18 at 17:55
Thanks @richard - FWIW though, if you're on anything older than SQL 2008R2, you're outside of MSFT's (extended) support window and should look to update. Of course, that might not stop your workplace from continuing to use 2005 or whatever anyway... – Dan Field Mar 16 '18 at 19:45
Is there an online utility that can take the XML and show the data in a tabular form - online in the browser? – Moiz Tankiwala Aug 24 '19 at 07:35
To add line breaks in the output for easier reading, use `PRINT REPLACE(CONVERT(NVARCHAR(MAX), @xmltmp), '><', '>' + CHAR(10) + '<')`. – djk Jun 03 '22 at 11:12
You can convert it to JSON as well in some versions of SQL server, just use FOR JSON instead of FOR XML – uhum Jul 14 '22 at 19:42

score 92 · Accepted Answer · answered Dec 22 '09 at 02:55

92

You know, there might be an easier way but the first thing that pops to mind is:

Declare @SumVal int;
Select @SumVal=Sum(Amount) From Expense;
Print @SumVal;

You can, of course, print any number of fields from the table in this way. Of course, if you want to print all of the results from a query that returns multiple rows, you'd just direct your output appropriately (e.g. to Text).

answered Dec 22 '09 at 02:55

Mark Brittingham

28,545
12
80
110

2

This is usually the best way, but for a nice solution when you have lots of rows & columns that you want to dump out with `print` see @DanFields answer below - http://stackoverflow.com/a/36729681/8479 – Rory Apr 24 '16 at 20:02

score 36 · Answer 3 · answered Oct 09 '12 at 12:17

If you want to print multiple rows, you can iterate through the result by using a cursor. e.g. print all names from sys.database_principals

DECLARE @name nvarchar(128)

DECLARE cur CURSOR FOR
SELECT name FROM sys.database_principals

OPEN cur

FETCH NEXT FROM cur INTO @name;
WHILE @@FETCH_STATUS = 0
BEGIN   
PRINT @name
FETCH NEXT FROM cur INTO @name;
END

CLOSE cur;
DEALLOCATE cur;

score 9 · Answer 4 · answered Dec 22 '09 at 02:54

9

set @n = (select sum(Amount) from Expense)
print 'n=' + @n

answered Dec 22 '09 at 02:54

jspcal

50,847
7
72
76

Jason Foglia · Answer 5 · 2016-02-22T16:52:19.017

I wrote this SP to do just what you want, however, you need to use dynamic sql.

This worked for me on SQL Server 2008 R2

ALTER procedure [dbo].[PrintSQLResults] 
    @query nvarchar(MAX),
    @numberToDisplay int = 10,
    @padding int = 20
as

SET NOCOUNT ON;
SET ANSI_WARNINGS ON;

declare @cols nvarchar(MAX), 
        @displayCols nvarchar(MAX), 
        @sql nvarchar(MAX), 
        @printableResults nvarchar(MAX),
        @NewLineChar AS char(2) = char(13) + char(10),
        @Tab AS char(9) = char(9);

if exists (select * from tempdb.sys.tables where name = '##PrintSQLResultsTempTable') drop table ##PrintSQLResultsTempTable

set @query = REPLACE(@query, 'from', ' into ##PrintSQLResultsTempTable from');
--print @query
exec(@query);
select ROW_NUMBER() OVER (ORDER BY (select Null)) AS ID12345XYZ, * into #PrintSQLResultsTempTable 
from ##PrintSQLResultsTempTable
drop table ##PrintSQLResultsTempTable

select name
into #PrintSQLResultsTempTableColumns
from tempdb.sys.columns where object_id =
object_id('tempdb..#PrintSQLResultsTempTable');

select @cols =
stuff((
    (select ' + space(1) + (LEFT( (CAST([' + name + '] as nvarchar(max)) + space('+ CAST(@padding as nvarchar(4)) +')), '+CAST(@padding as nvarchar(4))+')) ' as [text()]
    FROM #PrintSQLResultsTempTableColumns
    where name != 'ID12345XYZ'
    FOR XML PATH(''), root('str'), type ).value('/str[1]','nvarchar(max)'))
,1,0,'''''');

select @displayCols =
stuff((
    (select space(1) + LEFT(name + space(@padding), @padding) as [text()]
    FROM #PrintSQLResultsTempTableColumns
    where name != 'ID12345XYZ'
    FOR XML PATH(''), root('str'), type ).value('/str[1]','nvarchar(max)'))
,1,0,'');

DECLARE 
    @tableCount int = (select count(*) from #PrintSQLResultsTempTable);
DECLARE 
    @i int = 1, 
    @ii int = case when @tableCount < @numberToDisplay then @tableCount else @numberToDisplay end;

print @displayCols -- header
While @i <= @ii
BEGIN
    set @sql = N'select @printableResults = ' + @cols + ' + @NewLineChar from #PrintSQLResultsTempTable where ID12345XYZ = ' + CAST(@i as varchar(3)) + '; print @printableResults;'
    --print @sql
    execute sp_executesql @sql, N'@NewLineChar char(2), @printableResults nvarchar(max) output', @NewLineChar = @NewLineChar, @printableResults = @printableResults output
    print @printableResults
    SET @i += 1;
END

This worked for me on SQL Server 2012

ALTER procedure [dbo].[PrintSQLResults] 
    @query nvarchar(MAX),
    @numberToDisplay int = 10,
    @padding int = 20
as

SET NOCOUNT ON;
SET ANSI_WARNINGS ON;

declare @cols nvarchar(MAX), 
        @displayCols nvarchar(MAX), 
        @sql nvarchar(MAX), 
        @printableResults nvarchar(MAX),
        @NewLineChar AS char(2) = char(13) + char(10),
        @Tab AS char(9) = char(9);

if exists (select * from tempdb.sys.tables where name = '##PrintSQLResultsTempTable') drop table ##PrintSQLResultsTempTable

set @query = REPLACE(@query, 'from', ' into ##PrintSQLResultsTempTable from');
--print @query
exec(@query);
select ROW_NUMBER() OVER (ORDER BY (select Null)) AS ID12345XYZ, * into #PrintSQLResultsTempTable 
from ##PrintSQLResultsTempTable
drop table ##PrintSQLResultsTempTable

select name
into #PrintSQLResultsTempTableColumns
from tempdb.sys.columns where object_id =
object_id('tempdb..#PrintSQLResultsTempTable');

select @cols =
stuff((
    (select ' + space(1) + LEFT(CAST([' + name + '] as nvarchar('+CAST(@padding as nvarchar(4))+')) + space('+ CAST(@padding as nvarchar(4)) +'), '+CAST(@padding as nvarchar(4))+') ' as [text()]
    FROM #PrintSQLResultsTempTableColumns
    where name != 'ID12345XYZ'
    FOR XML PATH(''), root('str'), type ).value('/str[1]','nvarchar(max)'))
,1,0,'''''');

select @displayCols =
stuff((
    (select space(1) + LEFT(name + space(@padding), @padding) as [text()]
    FROM #PrintSQLResultsTempTableColumns
    where name != 'ID12345XYZ'
    FOR XML PATH(''), root('str'), type ).value('/str[1]','nvarchar(max)'))
,1,0,'');

DECLARE 
    @tableCount int = (select count(*) from #PrintSQLResultsTempTable);
DECLARE 
    @i int = 1, 
    @ii int = case when @tableCount < @numberToDisplay then @tableCount else @numberToDisplay end;

print @displayCols -- header
While @i <= @ii
BEGIN
    set @sql = N'select @printableResults = ' + @cols + ' + @NewLineChar   from #PrintSQLResultsTempTable where ID12345XYZ = ' + CAST(@i as varchar(3)) + ' '
    --print @sql
    execute sp_executesql @sql, N'@NewLineChar char(2), @printableResults nvarchar(max) output', @NewLineChar = @NewLineChar, @printableResults = @printableResults output
    print @printableResults
    SET @i += 1;
END

This worked for me on SQL Server 2014

ALTER procedure [dbo].[PrintSQLResults] 
    @query nvarchar(MAX),
    @numberToDisplay int = 10,
    @padding int = 20
as

SET NOCOUNT ON;
SET ANSI_WARNINGS ON;

declare @cols nvarchar(MAX), 
        @displayCols nvarchar(MAX), 
        @sql nvarchar(MAX), 
        @printableResults nvarchar(MAX),
        @NewLineChar AS char(2) = char(13) + char(10),
        @Tab AS char(9) = char(9);

if exists (select * from tempdb.sys.tables where name = '##PrintSQLResultsTempTable') drop table ##PrintSQLResultsTempTable

set @query = REPLACE(@query, 'from', ' into ##PrintSQLResultsTempTable from');
--print @query
exec(@query);
select ROW_NUMBER() OVER (ORDER BY (select Null)) AS ID12345XYZ, * into #PrintSQLResultsTempTable 
from ##PrintSQLResultsTempTable
drop table ##PrintSQLResultsTempTable

select name
into #PrintSQLResultsTempTableColumns
from tempdb.sys.columns where object_id =
object_id('tempdb..#PrintSQLResultsTempTable');

select @cols =
stuff((
    (select ' , space(1) + LEFT(CAST([' + name + '] as nvarchar('+CAST(@padding as nvarchar(4))+')) + space('+ CAST(@padding as nvarchar(4)) +'), '+CAST(@padding as nvarchar(4))+') ' as [text()]
    FROM #PrintSQLResultsTempTableColumns
    where name != 'ID12345XYZ'
    FOR XML PATH(''), root('str'), type ).value('/str[1]','nvarchar(max)'))
,1,0,'''''');

select @displayCols =
stuff((
    (select space(1) + LEFT(name + space(@padding), @padding) as [text()]
    FROM #PrintSQLResultsTempTableColumns
    where name != 'ID12345XYZ'
    FOR XML PATH(''), root('str'), type ).value('/str[1]','nvarchar(max)'))
,1,0,'');

DECLARE 
    @tableCount int = (select count(*) from #PrintSQLResultsTempTable);
DECLARE 
    @i int = 1, 
    @ii int = case when @tableCount < @numberToDisplay then @tableCount else @numberToDisplay end;

print @displayCols -- header
While @i <= @ii
BEGIN
    set @sql = N'select @printableResults = concat(@printableResults, ' + @cols + ', @NewLineChar) from #PrintSQLResultsTempTable where ID12345XYZ = ' + CAST(@i as varchar(3))
    --print @sql
    execute sp_executesql @sql, N'@NewLineChar char(2), @printableResults nvarchar(max) output', @NewLineChar = @NewLineChar, @printableResults = @printableResults output
    print @printableResults
    SET @printableResults = null;
    SET @i += 1;
END

Example:

exec [dbo].[PrintSQLResults] n'select * from MyTable'

This SP is not working, whatever query i give it's says `incorrect syntax near near 'my query'` — Ali Umair, Feb 21 '16 at 08:49
This works for me on SQL server 2014. Testing other versions now. — Jason Foglia, Feb 22 '16 at 16:26
Updated the post to include working versions on 2008 R2, 2012, and 2014. — Jason Foglia, Feb 22 '16 at 16:54
Thanks for the update, working fine now. but in one table i got this error `Msg 529, Level 16, State 2, Line 1 Explicit conversion from data type timestamp to nvarchar is not allowed. ` i'll try to fix this conversion issue — Ali Umair, Feb 23 '16 at 06:56
Just need a small fix for NULL values. select @cols = stuff(( (select ' , space(1) + LEFT(ISNULL(CAST([' + name + '] as nvarchar('+CAST(@padding as nvarchar(4))+')), '''') + space('+ CAST(@padding as nvarchar(4)) +'), — Ercument Eskar, Aug 09 '22 at 12:17

Dhana · Answer 6 · 2017-10-17T14:11:30.970

2

Try this query

DECLARE @PrintVarchar nvarchar(max) = (Select Sum(Amount) From Expense)
PRINT 'Varchar format =' + @PrintVarchar

DECLARE @PrintInt int = (Select Sum(Amount) From Expense)
PRINT @PrintInt

edited Oct 17 '17 at 14:11

answered Oct 17 '17 at 00:50

Dhana

1,618
4
23
39

score 2 · Answer 7 · answered Oct 16 '20 at 11:45

If you want to print more than a single result, just select rows into a temporary table, then select from that temp table into a buffer, then print the buffer:

drop table if exists #temp

-- we just want to see our rows, not how many were inserted
set nocount on

select * into #temp from MyTable

-- note: SSMS will only show 8000 chars
declare @buffer varchar(MAX) = ''

select @buffer = @buffer + Col1 + ' ' + Col2 + CHAR(10) from #temp

print @buffer

score 1 · Answer 8 · edited Jun 14 '18 at 22:48

1

Add

PRINT 'Hardcoded table name -' + CAST(@@RowCount as varchar(10))

immediately after the query.

edited Jun 14 '18 at 22:48

Sach

10,091
8
47
84

answered Jun 14 '18 at 19:42

Sena M

51
2

score 0 · Answer 9 · answered Aug 22 '18 at 16:49

You can also use the undocumented sp_MSforeachtable stored procedure as such if you are looking to do this for every table:

sp_MSforeachtable @command1 ="PRINT 'TABLE NAME: ' + '?' DECLARE @RowCount INT SET @RowCount = (SELECT COUNT(*) FROM ?) PRINT @RowCount"

mrduncle1 · Answer 10 · 2018-11-11T21:14:10.087

0

If you wish (like me) to have results containing mulitple rows of various SELECT queries "labelled" and can't manage this within the constraints of the PRINT statement in concert with the Messages tab you could turn it around and simply add messages to the Results tab per the below:

SELECT 'Results from scenario 1'
SELECT
    *
FROM tblSample

edited Nov 11 '18 at 21:14

answered Nov 09 '18 at 05:50

mrduncle1

589
7
11

that does not quite answer the question but does work for me. => upvote – rominator007 Dec 17 '20 at 05:46

Jason Doss · Answer 11 · 2022-11-15T20:05:20.537

0

Try this:

DECLARE @select as nvarchar(max) = ''
SELECT @select  = @select  + somefield + char(13) FROM sometable
PRINT @select

edited Nov 15 '22 at 20:05

answered Nov 15 '22 at 20:05

Jason Doss

1
1

SQL Server PRINT SELECT (Print a select query result)?

11 Answers11

Linked