RSS 2.0 Feed
RSS 2.0


Atom 1.0 Feed
Atom 1.0

  T-SQL Olympics 


qt8gt0bxhw|20009F4EEE83|RyanMain|subtext_Content|Text|0xfbffec0000000000a800000001000600

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.




                   



Leave a comment below.

Comments

  1. Digging My Blog - Dan Hounshell 10/26/2004 10:25 AM
    Gravatar
  2. the blog of michael eaton 10/26/2004 8:37 PM
    Gravatar
  3. Ernst Kuschke 10/29/2004 1:58 PM
    Gravatar
  4. Adam 2/8/2005 3:49 AM
    Gravatar
    whats in sql2pdf
  5. Ryan Farley 2/8/2005 4:36 AM
    Gravatar
    Adam,

    sql2pdf is a stored proc. You can get the proc at the link in the post on SqlServerCentral.com

    -Ryan
  6. T. Hoecherl 4/25/2005 12:29 PM
    Gravatar
    Ryan,

    This is major cool! However, I need to get an image into the .pdf (a logo). But I can't select image data into a local variable. Any ideas how I might do this?

    T
  7. sal 8/22/2005 10:13 AM
    Gravatar
    store as a BLOB
  8. Ivica Masar 8/26/2005 9:42 AM
    Gravatar
    It is Masar, not Mascar,
    but thanks anyway.
  9. Ryan Farley 8/26/2005 10:00 AM
    Gravatar
    Oops. Sorry Masar. At least I did get it right the first time I typed it ;-)
  10. quitchat 3/26/2006 7:43 AM
    Gravatar
    Impressive!!
  11. Shaly 6/22/2006 3:56 PM
    Gravatar
    Hi, there:

    I got an interesting question regarding T-SQL.

    Given 2 tables as following:
    -------------------------------------------
    Table 1: Customer
    1. CustID, char(5)
    2. Customer_Name varchar(50)

    Table 2: Sales
    1. invoice_date datetime
    2. amount decimal (10,2)
    3. CustID, char(5)
    _______________________________

    Write a T-SQL statement to output sales total by customers by year as following:

    ---------------------------------------------------------
    Customer_Name 2001 2002 2003 2004
    _________________________________________
    ABC company 500 101 0 1000
    -------------------------------------------------------------


    Thanks for help! =:)

    Shaly
  12. Solution to Shaly's Problem 12/16/2006 5:41 AM
    Gravatar
    1. create Customer and sales table and put some data into in it.


    2.
    Select C.CustID,C.Customer_Name,Datepart(yyyy,S.invoice_date)As Invoice_year,S.invoice_date,S.amount
    Into t
    FROM Customer C INNER JOIN Sales S ON (C.CustID = S.CustID)

    3.

    Select Customer_Name,[2001] as '2001',[2002] as '2002',[2003] as '2003',[2004] as '2004'
    FROM T
    PIVOT
    (
    SUM(Amount)FOR invoice_Year in ([2001],[2002],[2003],[2004])
    )AS T1


    This would get your result set
  13. Solution to Shaly's Problem 12/16/2006 5:42 AM
    Gravatar
    1. create Customer and sales table and put some data into in it.


    2.
    Select C.CustID,C.Customer_Name,Datepart(yyyy,S.invoice_date)As Invoice_year,S.invoice_date,S.amount
    Into t
    FROM Customer C INNER JOIN Sales S ON (C.CustID = S.CustID)

    3.

    Select Customer_Name,[2001] as '2001',[2002] as '2002',[2003] as '2003',[2004] as '2004'
    FROM T
    PIVOT
    (
    SUM(Amount)FOR invoice_Year in ([2001],[2002],[2003],[2004])
    )AS T1


    This would get your result set


    ---Ashwani Roy
  14. Prabhat Sahoo 2/2/2007 10:17 AM
    Gravatar
    can any body let me know how to create PDF file with Image [color ] and Tables and using SQL2PDF procedure ...
  15. JDevenney 2/8/2007 7:09 AM
    Gravatar
    Hi

    I just have a quick question I am running SQL Server 2005 and when I execute the procedure as described, it runs ok but does not generate the pdf can anyone help

    Thanks
    Jason
  16. Tanya 3/25/2008 11:06 AM
    Gravatar
    Inside of procedure, how can i modify font size, page layout?
Comments have been closed on this topic.



 

News


Also see my CRM Developer blog

Connect:   @ryanfarley@mastodon.social

         

Sponsor

Sections