SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  ON 
GO

/****** Object:  Stored Procedure dbo.sp_tableGen    Script Date: 10/22/00 12:20:15 PM ******/
/*
STORED PROCEDURE TO GENERATE AN HTML TABLE USING CURSOR AND CURSOR METADA
AUTHOR: LES SMITH
DATE: OCTOBER 22, 2000

MAIN STEPS:

1) USER ENTERS SQL QUERY AND TABLE ATTRIBUTES AS ARGUMENTS FOR sp_tableGen
2) CURSOR FOR THE ENTERED SQL STATEMENT IS DECLARED USING DYNAMIC SQL, NAME: tabCur
3) A QUERY IS MADE TO SYSTEM TABLES TO GET METADATA FOR THE CURSOR (COLUMN NAMES), NAME: cols
4) COLUMN NAMES FOR THE CURSOR ARE SAVED IN A TEMPORARY TABLE
5) A TEMPORARY TABLE OF ONE STRING COLUMN  IS CREATED TO HOLD THE HTML TABLE CODE, NAME: WebTable
6) USING THE NAMES IN THE  cols  TEMPORARY TABLES WE GENERATE THE VARIABLES TO HOLD THE FETCH FROM tabCur
7) DECLARATION OF VARIABLES AND FETCH NEED TO BE RUN IN ONE DYNAMIC SQL STATEMENT
8) DATA FROM THE FETCH AND HTML WRAPPERS ARE WRITTEN TO THE WebTable
9) A SELECT IS PERFORMED ON THE WebTable AND A RESULTSET IS STREAMED TO THE ASP PAGE WITH A FULLY FORMED TABLE


*/

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



DECLARE @column_name  VARCHAR(100)
DECLARE @column_num INT
DECLARE @column_count INT
DECLARE @declaration_String  VARCHAR(1000)
DECLARE  @cursor_variables  VARCHAR(1000)
DECLARE @html_cursor_variables VARCHAR(1000)


-------INITIALIZE VARIABLES TO ZERO LENGTH STRING---------------
 SELECT @declaration_String  = ' '
 SELECT @cursor_variables  = ' '
 SELECT @html_cursor_variables  = ''

------------------DECLARE THE CURSOR USING THE SELECT STATEMENT THAT USER HAS ENTERED-----------------------
EXEC ('DECLARE tabCur CURSOR FOR  '+ @cursorString)


-------------------CREATE THE TEMPORARY TABLE TO BE USED FOR HOLDING THE HTML TABLE
CREATE TABLE #webTable
(  htmlString VARCHAR(2000))


-------------------CREATE A TEMPORARY TABLE TO HOLD THE COLUMN NAMES----------------------------------
CREATE TABLE #cols
(ColNum  INT  IDENTITY (1, 1) NOT NULL ,
ColName VARCHAR(100)
)


---------------INSERT HTML TO BEGIN A TABLE--------------------------------------------------------------------
INSERT INTO  #webTable ( htmlString) VALUES ('<TABLE' + @tableAttributes + ' >')


-------------CREATE A CURSOR FOR THE SELECT STATEMENT'S COLUMN NAMES-----------------
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


----BEGIN  THE HEADER  ROW
INSERT INTO  #webTable ( htmlString) VALUES ('<TR>')

-----------OPEN AND FETCH THE CURSOR OF COLUMN NAMES----------------------------------------------------------
OPEN  colCur  
FETCH NEXT FROM colCur  INTO  @column_name, @column_num,  @column_count



-------CREATE THE SQL STATEMENTS BASED ON METADATA
WHILE (@@FETCH_STATUS = 0)
BEGIN
	---INSERT COLUMN NAMES INTO THE cols TABLE
	INSERT INTO  #cols( ColName) VALUES ( @column_name)
--------ADD THE COLUMN HEADER NAMES USING THE CURSOR OF COLUMN NAMES --------------------------------------------------
	INSERT INTO  #webTable ( htmlString) VALUES ('<TD>'+  @column_name + '</TD>' )
	 SELECT @declaration_String  =   @declaration_String  + '  DECLARE @' + @column_name +  '  VARCHAR(100)   ' + CHAR(13)
		
----------------BUILD THE STRINGS FOR DYNAMIC SQL BASED ON COLUMN'S POSITION
	IF ( @column_num +1)  <  @column_count 
	BEGIN
-----------------CREATE A STRING OF  VARIABLES TO USE IN DYNAMIC SQL STATEMENT TO LOOP THROUGH  CURSOR 
		 SELECT @cursor_variables  =  @cursor_variables + ' @' + @column_name + ', '
-----------------CREATE A STRING OF ONE ROW IN THE HTML TABLE----------------------------------------------------------------------------------
		SELECT @html_cursor_variables =  @html_cursor_variables    +    '   '' <TD>''   + @'+     @column_name    +     '  +   '' </TD>'' +    '
		
	END
	ELSE
		BEGIN
-----------------CREATE A STRING OF  VARIABLES TO USE IN DYNAMIC SQL STATEMENT TO LOOP THROUGH  CURSOR 	
		SELECT @cursor_variables  =  @cursor_variables + ' @' + @column_name	
-----------------CREATE A STRING OF ONE ROW IN THE HTML TABLE----------------------------------------------------------------------------------
		SELECT @html_cursor_variables =  @html_cursor_variables    +    '   '' <TD>''   + @'+     @column_name    +     '  +   '' </TD>''    '
	
		END
		

	FETCH NEXT FROM colCur  INTO  @column_name, @column_num,  @column_count

END


-----------------END  THE HEADER  ROW------------------------------
INSERT INTO  #webTable ( htmlString) VALUES ('</TR>')


--------------OPEN THE CURSOR FOR QUERY ENTERED BY USER AND GENERATE TABLE TAGS-------------------------
OPEN  tabCur 

declare @curStr VARCHAR(2000)

--USE DYNAMIC SQL TO DECLARE VARIABLES AND SCROLL THROUGH CURSOR
SELECT  @curStr  =   @declaration_String + CHAR(13) +
'  FETCH NEXT FROM  tabCur INTO '+ @cursor_variables + CHAR(13) +
'  WHILE (@@FETCH_STATUS= 0) '+CHAR(13) +
	'  BEGIN  ' +CHAR(13) +

	--BEGIN ROW
	'INSERT INTO  #webTable ( htmlString) VALUES (''<TR>'')  ' + CHAR(13) +

	    --ADD THE ROW OF COLUMN NAMES THAT HAVE BEEN ASSEMBLED TOGETHER IN A STRING
	     'INSERT INTO  #webTable ( htmlString) VALUES ( '+ @html_cursor_variables +') '+
	
	
	--END ROW
	'INSERT INTO  #webTable ( htmlString) VALUES (''</TR>'')  ' +CHAR(13) +

	' FETCH NEXT FROM  tabCur INTO  '+ @cursor_variables +CHAR(13) +

' END'

EXEC  ( @curStr)

--CLOSING CODE FOR THE   HTML TABLE
INSERT INTO  #webTable ( htmlString) VALUES ('</TABLE>')


------CLOSE  AND DEALLOCATE  ALL CURSORS
CLOSE  tabCur 
DEALLOCATE  tabCur 

CLOSE colCur 
DEALLOCATE  colCur 

----OUTPUT THE RECORDSET OF HTML TABLE  FROM THE TEMPORARY TABLE
SELECT  htmlString  FROM  #webTable





GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO