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

Sql ServerTsqlSelect Query

Sql Server Problem Overview

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

Example:

PRINT 
    SELECT SUM(Amount) FROM Expense

Sql Server Solutions

Solution 1 - Sql Server

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

Solution 2 - Sql Server

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

Solution 3 - Sql Server

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;

Solution 4 - Sql Server

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

Solution 5 - Sql Server

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'

Solution 6 - Sql Server

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

Solution 7 - Sql Server

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

Solution 8 - Sql Server

Add

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

immediately after the query.

Solution 9 - Sql Server

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

enter image description here

Solution 10 - Sql Server

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"
Categories
Recommended Sql Server Solutions
Recommended Tsql Solutions
Recommended Select Query Solutions

Previous Solution:

How to retrieve the android sdk version?

AndroidSdkVersion

Next Solution:

Difference between dates in JavaScript

JavascriptDatetimeDatediff

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionShimmy WeitzhandlerView Question on Stackoverflow
Solution 1 - Sql ServerMark BrittinghamView Answer on Stackoverflow
Solution 2 - Sql ServerDan FieldView Answer on Stackoverflow
Solution 3 - Sql ServerStefanView Answer on Stackoverflow
Solution 4 - Sql ServerjspcalView Answer on Stackoverflow
Solution 5 - Sql ServerJason FogliaView Answer on Stackoverflow
Solution 6 - Sql ServerDhanaView Answer on Stackoverflow
Solution 7 - Sql ServerGary ChapmanView Answer on Stackoverflow
Solution 8 - Sql ServerSena MView Answer on Stackoverflow
Solution 9 - Sql Servermrduncle1View Answer on Stackoverflow
Solution 10 - Sql Serversmoore4View Answer on Stackoverflow