SQL Server – Printing String Before Returning Table Results

sql servert-sql

I’m using the following query to generate a summary of invoices for a particular customer. 

SELECT
customer_account,
store_number,
invoice_total

FROM invoices WHERE company LIKE '%Joe’s Bait Shop%';

This SQL query is used as an input in a homegrown application which executes the query against the DB, and outputs the results to an Excel spreadsheet. The output looks something like:

customer_account    store_number    invoice_total
222                 13              22.90
333                 16              56.00
444                 21              18.21

Before this spreadsheet can be sent to the customer, text or “header” information must be manually added to the file such as:

Frank’s Fish Supply
Remit To
123 First St., Wonderland, HI 12345

Is there anything I could add to the SQL query that display this text in the output, before returning the table data? Ideally, I would want the SQL query to return:

Frank’s Fish Supply
Remit To
123 First St., Wonderland, HI 12345

customer_account    store_number    invoice_total
222                 13              22.90
333                 16              56.00
444                 21              18.21

I tried using something like:

PRINT N’Frank’s Fish Supply’;
PRINT N’Remit To’;
PRINT N’123 First St., Wonderland, HI 12345’;
PRINT N’’;

SELECT
customer_account,
store_number,
invoice_total

FROM invoices WHERE company LIKE '%Joe’s Bait Shop%';

However, the results of the PRINT commands only show up in the “Messages” tab of SSMS rather than the “Results” tab, and do not appear in the Excel output when run through the homegrown application. Are there any SQL commands I could use to output this static text before the results of SELECT statement?

Best Answer

I stumbled upon this thread which contained a suitable solution. I ended using a query similar to the following to generate my desired results:

select customer_account, store_number, invoice_total
from ((select 'Frank’s Fish Supply' as customer_account, '' as store_number, '' as invoice_total, 1 as which
      ) union all
        (select 'Remit To:' as customer_account, '' as store_number, '' as invoice_total, 2 as which
      ) union all
        (select '123 First St., Wonderland, HI 12345' as customer_account, '' as store_number, '' as invoice_total, 3 as which
      ) union all
        (select '' as customer_account, '' as store_number, '' as invoice_total, 4 as which
      ) union all
        (select 'customer_account' as customer_account, 'store_number' as store_number, 'invoice_total' as invoice_total, 5 as which
      ) union all
      (select customer_account, store_number, invoice_total, 6 as which from arinvc where company LIKE '%Joe’s Bait Shop%'
      )
     ) t
order by which;
Related Question