sql-server - valor - print select sql server

SQL Server PRINT SELECT(¿Imprimir un resultado de consulta de selección)?

Question

Estoy tratando de imprimir un valor seleccionado, ¿es esto posible?

Ejemplo:

PRINT
SELECT SUM(Amount) FROM Expense

Answer 1

Añadir

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

inmediatamente después de la consulta.

Answer 2

Escribí este SP para hacer exactamente lo que quiere, sin embargo, necesita usar sql dinámico.

Esto funcionó para mí en 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

Esto funcionó para mí en 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

Esto funcionó para mí en 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

Ejemplo:

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

Answer 3

Prueba esta consulta

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

Answer 4

Si desea imprimir varias filas, puede recorrer el resultado utilizando un cursor. por ejemplo, imprimir todos los nombres de 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;

Answer 5

Si estás de acuerdo con verlo como XML:

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

Si bien la pregunta de OP tal como se solicitó no necesariamente requiere esto, es útil si usted está buscando imprimir múltiples filas / columnas (dentro de lo razonable).

Answer 6

También puede usar el procedimiento almacenado sp_MSforeachtable no sp_MSforeachtable como tal si desea hacer esto para cada tabla:

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

Answer 7

Ya sabes, podría haber una manera más fácil, pero lo primero que me viene a la mente es:

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

Por supuesto, puede imprimir cualquier número de campos de la tabla de esta manera. Por supuesto, si desea imprimir todos los resultados de una consulta que devuelve varias filas, simplemente dirija su salida de forma adecuada (por ejemplo, a Texto).

Answer 8

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