Using Cursor Metadata to Dynamically Generate HTML Tables
The following stored procedure will demonstrate the use of cursor metadata.
Using cursor metadata we can get information about an SQL statement and use that
information to dynamically generate other code such as HTML or other stored
procedures. In this example we will be taking an SQL statement as an argument in
our stored procedure. Using the information we get from the cursor metadata
tables (master.dbo.syscursorcolumns and master.dbo.syscursors) we will
dynamically generate the HTML code for a table.
The same task may of course be accomplished by using ASP methods to build a
table. But using an SQL Server stored procedure to deliver a pre-built recordset
offers the advantages of centralized code, as well as the ability to
persist(store) the a pre-built recordset as long as needed. In my example I am
using a local temporary table(disappears at end of the procedure) to store the
HTML table. But you might also look into creating global temporary tables (can
be saved in memory until no longer needed) to persist for a longer period of
time, or using permanent tables to store the data even longer.
Code: sp_tableGen.txt
Implementation
The stored procedure builds the HTML table in the
following way:
User enters sql query and table attributes as arguments for the stored
procedure.
CREATE PROCEDURE sp_tableGen
--INPUT THE QUERY FOR THE CURSOR
@cursorString VARCHAR(1000) = ' SELECT CustomerID, CompanyName FROM CUSTOMERS',
--INPUT ANY TABLE ATTRIBUTES REQUIRED
@tableAttributes VARCHAR(500) = ' border=5 width=100% cellspacing=1 cellpadding=1 '
AS
Cursor for the entered sql statement is declared using dynamic sql, name:
tabcur
EXEC ('DECLARE tabCur CURSOR FOR '+ @cursorString)
A query is made to system tables to get metadata for the cursor (column
names), name: cols
DECLARE colCur CURSOR FOR SELECT A.column_name, A.ordinal_position, B.column_count
FROM master.dbo.syscursorcolumns A, master.dbo.syscursors B
WHERE A.cursor_handle = B.cursor_handle
AND B.cursor_name = 'tabCur'
ORDER BY ordinal_position
A temporary table of one string column is created to hold the html table
code, name: webtable
CREATE TABLE #webTable
( htmlString VARCHAR(2000))
A Fetch is performed on the cols cursor. In the cursor the following
actions are performed: a. Column names for the cursor are saved in a temporary
table
INSERT INTO #cols( ColName) VALUES ( @column_name)
b. We generate
VARCHAR variables to use in generating our fetch of tabCur Declaration of
variables and fetch need to be run in one dynamic sql statement. Data from the
fetch and html wrappers are written to the webtable when the dynamic SQL is
executed.
declare @curStr VARCHAR(2000)
A select is performed on the webtable and a resultset is streamed to the asp page with a fully formed table.
SELECT htmlString FROM #webTable
Further Information
In order to learn about cursor metadata I recommend that you study the following system stored procedures:
- sp_describe_cursor
- sp_describe_cursor_columns
- sp_describe_cursor_tables
