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

I开发者_高级运维 am trying to print a selected value, is this possible?

Example:

PRINT 
    SELECT SUM(Amount) FROM Expense

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

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;

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

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'

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

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

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

Try this:

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