SQL Server PRINT SELECT Print a select query result

Question

I am trying to print a selected value  is this possible   Example   PRINT      SELECT SUM Amount  FROM Expense

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

set  n    select sum Amount  from Expense  print  n      n

User · Answer

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

User · Answer

Add   PRINT  Hardcoded table name -    CAST   RowCount as varchar 10     immediately after the query

User · Answer

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  lt   numberToDisplay then  tableCount else  numberToDisplay end   print  displayCols -- header While  i  lt    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  lt   numberToDisplay then  tableCount else  numberToDisplay end   print  displayCols -- header While  i  lt    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  lt   numberToDisplay then  tableCount else  numberToDisplay end   print  displayCols -- header While  i  lt    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

User · Answer

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

User · Answer

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

[sql-server] SQL Server PRINT SELECT (Print a select query result)?

Examples related to sql-server

Examples related to tsql

Examples related to select-query