Creating a PDF from a Stored Procedure

This article explains how to create a a stored procedure that will in turn create a simple column based report in PDF without using any external tools or libraries (and their associated licensing costs!).

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)) 

After that create the stored procedure SQL2PDF.

SQL2PDF.TXT

And table psopdf has to be filled with your data as shown in examples below.
At the end the stored procedure is called using the file name only (not extension).

EXEC sql2pdf 'fileName'

The result is in your C:\ directory.

EXAMPLE 1:

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'

After INSERT call the stored procedure with file name demo2.

EXEC sql2pdf 'demo2'

The result is in your C:\ directory.

EXAMPLE 2:

Second example uses a database pubs.

USE pubs
INSERT psopdf(code) SELECT t1.au_lname + ' ' + t1.au_fname + ' ' + t1.phone 
  +
 ' ' + t1.address + ' ' + t1.city + ' ' + t1.state + ' ' + t1.zip FROM 
 authors t1, authors t2

After INSERT call the stored procedure with file name demo1.

EXEC sql2pdf 'demo1'

>The result is in your C:\ directory.