熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

T-SQL 存儲過程創建 PDF 格式文件報表

2013-11-15 14:34:15  來源: SQL Server 

  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!)
  
  SQLPDF makes a PDF report from text inserted in the table psopdf ( nvarchar() ) First a table named psopdf should be created
  
  CREATE TABLE psopdf (code NVARCHAR())
  After that create the stored procedure SQLPDF
  
  SQLPDFTXT
  
  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 sqlpdf fileName
  The result is in your C:\ directory
  
  EXAMPLE :
  INSERT psopdf(code) SELECT SPACE() + COMPANY LTD
  INSERT psopdf(code) SELECT SPACE() + COMPANY ADDRESS
  INSERT psopdf(code) SELECT SPACE() + STREET NAME & No
  INSERT psopdf(code) SELECT
  INSERT psopdf(code) SELECT SPACE() + BILL OF SALE
  INSERT psopdf(code) SELECT
  INSERT psopdf(code) SELECT Product + SPACE() + Quantity
  + SPACE() + Price + SPACE() + Total
  INSERT psopdf(code) SELECT REPLACE(SPACE() _)
  INSERT psopdf(code) SELECT Product + SPACE() +
  + SPACE() + + SPACE() +
  INSERT psopdf(code) SELECT Product + SPACE() +
  + SPACE() + + SPACE() +
  INSERT psopdf(code) SELECT REPLACE(SPACE() _)
  INSERT psopdf(code) SELECT SPACE() +
  
  After INSERT call the stored procedure with file name demo
  
  EXEC sqlpdf demo
  The result is in your C:\ directory
  
  
 

  EXAMPLE :
  Second example uses a database pubs
  
  USE pubs
  INSERT psopdf(code) SELECT tau_lname + + tau_fname + + tphone
  +
   + taddress + + tcity + + tstate + + tzip FROM
  authors t authors t
  
  After INSERT call the stored procedure with file name demo
  
  EXEC sqlpdf demo
  >The result is in your C:\ directory
  
educitycn/img_///gif>

  sqlpdftxt
  
   DROP PROCEDURE sqlpdf
  CREATE PROCEDURE sqlpdf
  @filename VARCHAR()
  AS
  CREATE TABLE #pdf (idnumber INT IDENTITY()
  code NVARCHAR())
  CREATE TABLE #xref (idnumber INT IDENTITY()
  code VARCHAR())
  CREATE TABLE #text (idnumber INT IDENTITY()
  code VARCHAR())
  
  DECLARE @end VARCHAR()
  @beg  VARCHAR()
  @a  VARCHAR()
  @a  VARCHAR()
  @ad  VARCHAR()
  @cr  VARCHAR()
  @pr  VARCHAR()
  @ti  VARCHAR()
  @xstr VARCHAR()
  @page VARCHAR()
  @pdf  VARCHAR()
  @trenutniRed NVARCHAR()
  @rows  INT
  @ofset INT
  @len  INT
  @nopg  INT
  @fs INT
  @ole  INT
  @x INT
  @file  INT
  @object INT
  SELECT @pdf = C:\ + @filename + pdf
  SET @page =
  SET @nopg =
  SET @object =
  SET @end = endobj
  SET @beg = obj
  SET @a = <<'
  SET @a2 = '>>
  SET @ad = R
  SET @cr = CHAR() + CHAR() + CHAR () + CHAR() + CHAR() + CHAR () + CHAR()
  SET @pr = CHAR() + CHAR() + CHAR () + CHAR() + CHAR() + CHAR ( ) + CHAR() + CHAR()
  SET @ti = CHAR() + CHAR() + CHAR () + CHAR() + CHAR()
  SET @xstr = n
  SET @ofset =
  INSERT INTO #xref(code) VALUES (xref)
  INSERT INTO #xref(code) VALUES ( )
  INSERT INTO #xref(code) VALUES ( f)
  INSERT INTO #xref(code) VALUES ( + @xstr)
  INSERT INTO #xref(code) VALUES ( + @xstr)
  INSERT INTO #xref(code) VALUES ( + @xstr)
  INSERT INTO #xref(code) VALUES ( + @xstr)
  INSERT INTO #xref(code) VALUES ( + @xstr)
  INSERT INTO #xref(code) VALUES ( + @xstr)
  INSERT INTO #xref(code) VALUES ( + @xstr)
  INSERT INTO #pdf (code) VALUES (% + CHAR() + CHAR() + CHAR () + )
  INSERT INTO #pdf (code) VALUES (%佑佑)
  INSERT INTO #pdf (code) VALUES ( + @beg)
  INSERT INTO #pdf (code) VALUES (@a)
  INSERT INTO #pdf (code) VALUES (/ + @cr + (Ivica Masar + CHAR() + CHAR() + CHAR () + CHAR() + CHAR() + CHAR () + ))
  INSERT INTO #pdf (code) VALUES (/ + @pr + (stored procedure for ms sql pso@viphr))
  INSERT INTO #pdf (code) VALUES (/ + @ti + (SQL + CHAR() + CHAR() + CHAR () + ))
  INSERT INTO #pdf (code) VALUES (@a)
  INSERT INTO #pdf (code) VALUES (@end)
  INSERT INTO #pdf (code) VALUES ( + @beg)
  INSERT INTO #pdf (code) VALUES (@a)
  INSERT INTO #pdf (code) VALUES (/Type /Font)
  INSERT INTO #pdf (code) VALUES (/Subtype /Type)
  INSERT INTO #pdf (code) VALUES (/Name /F)
  INSERT INTO #pdf (code) VALUES (/Encoding + @ad)
  INSERT INTO #pdf (code) VALUES (/BaseFont /Courier)
  INSERT INTO #pdf (code) VALUES (@a)
  INSERT INTO #pdf (code) VALUES (@end)
  INSERT INTO #pdf (code) VALUES ( + @beg)
  INSERT INTO #pdf (code) VALUES (@a)
  INSERT INTO #pdf (code) VALUES (/Type /Encoding)
  INSERT INTO #pdf (code) VALUES (/BaseEncoding /WinAnsiEncoding)
  INSERT INTO #pdf (code) VALUES (@a)
  INSERT INTO #pdf (code) VALUES (@end)
  INSERT INTO #pdf (code) VALUES ( + @beg)
  INSERT INTO #pdf (code) VALUES (@a)
  INSERT INTO #pdf (code) VALUES ( /Font + @a + /F + @ad + + @a +  /ProcSet [ / + CHAR() + CHAR() + CHAR () + /Text ])
  INSERT INTO #pdf (code) VALUES (@a)
  INSERT INTO #pdf (code) VALUES (@end)
  INSERT INTO #text(code) (SELECT code FROM psopdf)
  SELECT @x = COUNT(*) FROM #text
  SELECT @x = (@x / ) +
  WHILE @nopg < @x
  BEGIN
  DECLARE SysKursor INSENSITIVE SCROLL CURSOR
  FOR SELECT SUBSTRING((code + SPACE()) ) FROM #text WHERE idnumber BETWEEN ((@nopg * ) + ) AND ((@nopg + ) * )
  FOR READ ONLY
  OPEN SysKursor
  FETCH NEXT FROM SysKursor INTO @trenutniRed
  SELECT @object = @object +
  SELECT @page = @page +  + CAST(@object AS VARCHAR) + @ad
  SELECT @len = LEN(@object) + LEN(@object + )
  INSERT INTO #pdf (code) VALUES (CAST(@object AS VARCHAR) + @beg)
  INSERT INTO #pdf (code) VALUES (@a)
  INSERT INTO #pdf (code) VALUES (/Type /Page)
  INSERT INTO #pdf (code) VALUES (/Parent + @ad)
  INSERT INTO #pdf (code) VALUES (/Resources + @ad)
  SELECT @object = @object +
  INSERT INTO #pdf (code) VALUES (/Contents + CAST(@object AS VARCHAR) + @ad)
  INSERT INTO #pdf (code) VALUES (@a)
  INSERT INTO #pdf (code) VALUES (@end)
  SELECT @ofset = @len + + @ofset
  INSERT INTO #xref(code) (SELECT SUBSTRING( + CAST(@ofset AS VARCHAR)
  LEN( + CAST(@ofset AS VARCHAR))
  LEN( + CAST(@ofset AS VARCHAR))) + @xstr)
  INSERT INTO #pdf (code) VALUES (CAST(@object AS VARCHAR) + @beg)
  INSERT INTO #pdf (code) VALUES (@a)
  SELECT @object = @object +
  INSERT INTO #pdf (code) VALUES (/Length + CAST(@object AS VARCHAR) + @ad)
  INSERT INTO #pdf (code) VALUES (@a)
  INSERT INTO #pdf (code) VALUES (stream)
  INSERT INTO #pdf (code) VALUES (BT)
  INSERT INTO #pdf (code) VALUES (/F
From:http://tw.wingwit.com/Article/program/SQLServer/201311/21997.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.