OK, there is not an Olympics for T-SQL - but there should be. A friend of mine and I were talking the other day about “gold-medal” T-SQL we've come accross. The one that wins the gold for me is the code I found a long time ago on SQL Server Central from Masar Ivica that creates a PDF file from scratch in a stored procedure. No external tools used. No PDF libraries or anything, just good old T-SQL (plus use of the FileSystemObject to write out the file itself - but still all done in the stored proc).

SQL2PDF makes a PDF report from text inserted in the table psopdf ( nvarchar(80) ). First a table named psopdf should be created.

CREATE TABLE psopdf (code NVARCHAR(80)) 

Then create the stored procedure SQL2PDF using SQL2PDF.txt from Mascar's SqlServerCentral.com article. Fill the 'psopdf' table with your data as shown in example below. Finally, call the stored procedure passing the file name only (no extension).

EXEC sql2pdf 'fileName'

Example:

INSERT psopdf(code) SELECT SPACE(60) + 'COMPANY LTD'
INSERT psopdf(code) SELECT SPACE(60) + 'COMPANY ADDRESS'
INSERT psopdf(code) SELECT SPACE(60) + 'STREET NAME & No'
INSERT psopdf(code) SELECT ' '
INSERT psopdf(code) SELECT SPACE(34) + 'BILL OF SALE'
INSERT psopdf(code) SELECT ' '
INSERT psopdf(code) SELECT 'Product' + SPACE(10) + 'Quantity'
       + SPACE(10) + 'Price' + SPACE(10) + 'Total'
INSERT psopdf(code) SELECT REPLACE(SPACE(56), ' ', '_')
INSERT psopdf(code) SELECT 'Product1' + SPACE(9) + '10.00 '
       + SPACE(10) + '52.30' + SPACE(10) + '5230.0'
INSERT psopdf(code) SELECT 'Product2' + SPACE(9) + '2.00 '
       + SPACE(10) + '10.00' + SPACE(10) + ' 20.0'
INSERT psopdf(code) SELECT REPLACE(SPACE(56), ' ', '_')
INSERT psopdf(code) SELECT SPACE(50) + '5250.0'

EXEC sql2pdf 'demo2'

The result is that the PDF shown below will be created in your C:\ root directory.

Now that's just plain cool in my book. Excellent work. Masar brings home the Gold.