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