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

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

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).

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;

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'

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).

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

Add

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

immediately after the query.

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"

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

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

Try this:

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