Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 22, 2000

Using Cursor Metadata to Dynamically Generate HTML Tables

By Les Smith

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


The stored procedure builds the HTML table in the following way:

User enters sql query and table attributes as arguments for the stored procedure.

@cursorString VARCHAR(1000) = ' SELECT CustomerID, 
@tableAttributes  VARCHAR(500) = 
'   border=5 width=100% cellspacing=1 cellpadding=1  '

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

(  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:

  1. sp_describe_cursor
  2. sp_describe_cursor_columns
  3. sp_describe_cursor_tables

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM