SQL Server PRINT SELECT (Print a select query result)?
Solution 1
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
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
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
set @n = (select sum(Amount) from Expense)
print 'n=' + @n
Solution 5
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'
![Shimmy Weitzhandler](https://lh4.googleusercontent.com/-6krMF6SwdIY/AAAAAAAAAAI/AAAAAAAADzE/5y9xrjn621Q/photo.jpg?sz=256)
Shimmy Weitzhandler
Updated on July 05, 2022Comments
-
Shimmy Weitzhandler almost 2 years
I am trying to print a selected value, is this possible?
Example:
PRINT SELECT SUM(Amount) FROM Expense
-
Ali Umair over 8 yearsThis SP is not working, whatever query i give it's says
incorrect syntax near near 'my query'
-
Jason Foglia over 8 yearsThis works for me on SQL server 2014. Testing other versions now.
-
Jason Foglia over 8 yearsUpdated the post to include working versions on 2008 R2, 2012, and 2014.
-
Ali Umair over 8 yearsThanks 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 -
irag10 about 8 yearsThis 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. -
irag10 about 8 yearsThis 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 - stackoverflow.com/a/36729681/8479 -
Dan Field about 8 yearsYeah, I've used this in SSMS where adding a SELECT * FROM would cause problems for other applications/users.
-
richard over 6 yearsOn older versions of SQL Server you need DECLARE
@xmltmp xml; SELECT @xmltmp = (SELECT * FROM table FOR XML AUTO); PRINT CONVERT(NVARCHAR(MAX), @xmltmp)
-
Dan Field over 6 yearsThanks @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...
-
Moiz Tankiwala almost 5 yearsIs there an online utility that can take the XML and show the data in a tabular form - online in the browser?
-
rominator007 over 3 yearsthat does not quite answer the question but does work for me. => upvote
-
djk about 2 yearsTo add line breaks in the output for easier reading, use
PRINT REPLACE(CONVERT(NVARCHAR(MAX), @xmltmp), '><', '>' + CHAR(10) + '<')
.