Dynamic SELECT Statement that outputs HTML code

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic SELECT Statement that outputs HTML code

Author

Topic

hsteckylf
Starting Member

5 Posts

Posted - 2004-08-19 : 12:31:17

This one is a doosey. I have been assigned the task of port our current .dll/ASP code into a SQL Server 2000 Stored Procedure. The purpose of this is to decrease the time it takes our ASP pages to load. I have done some tests on this, and running the code as-is (ad-hoc sql statement and then ASP parses through the recordset to display HTML) on ~700 records took 71 seconds to display. Running these same records with the sproc (Create one massive SELECT that will output HTML code) took 7 seconds, so I know this will work. My problem is that the HTML code needs to be in strings in the SELECT statement. ex:


SELECT 
	  '<tr><td width=5% align=center><input type=checkbox name="chkPerson" value="'+
	  cast(tblApplicantExport.UserID AS varchar)+'/'+cast(tblApplicantExport.PersonID AS varchar)+'/'+cast(tblApplicantExport.FormSetID AS varchar)+
	  '"></td><td align=left width=50>'+
	  (ISNULL (tblApplicants.FirstName, ''))+' '+
	  (ISNULL (tblApplicants.MiddleName, ''))+' '+'</td><td align=left> '+
	  (ISNULL (tblApplicants.LastName, ''))+
	  '</td><td align=left>(downloaded by '+cast(tblUserMaster.UserName AS varchar)+
	  ' on '+cast(tblApplicantExport.LastDownloadDate AS varchar)+
	  ')</td><td width=15% align=left><input type="hidden" name="hidEvent'+
	  cast(tblApplicantExport.UserID AS varchar)+cast(tblApplicantExport.PersonID AS varchar)+cast(tblApplicantExport.FormSetID AS varchar)+
	  '" value="'+cast(tblApplicantExport.EventID AS varchar)+'"><input type="hidden" name="hidAction'+
	  cast(tblApplicantExport.UserID AS varchar)+cast(tblApplicantExport.PersonID AS varchar)+cast(tblApplicantExport.FormSetID AS varchar)+
	  '" value="'+cast(tblApplicantExport.ActionID AS varchar)+'"><input type="hidden" name="hidFormset'+
	  cast(tblApplicantExport.UserID AS varchar)+cast(tblApplicantExport.PersonID AS varchar)+cast(tblApplicantExport.FormSetID AS varchar)+
	  '" value="'+cast(tblApplicantExport.FormSetID AS varchar)+'"><input type="hidden" name="hidUser'+
	  cast(tblApplicantExport.UserID AS varchar)+cast(tblApplicantExport.PersonID AS varchar)+cast(tblApplicantExport.FormSetID AS varchar)+
	  '" value="'+cast(tblApplicantExport.UserID AS varchar)+'"></td></tr>' AS oneRecord
	FROM tblApplicantExport 
	  INNER JOIN tblApplicants ON tblApplicantExport.PersonID = tblApplicants.PersonID 
	  INNER JOIN tblActions ON tblApplicantExport.ActionID = tblActions.ActionID 
	  INNER JOIN tblUserMaster ON tblApplicantExport.UserID =  tblUserMaster.UserID
	WHERE tblApplicantExport.ActionID = @PassedActionID AND 
	  tblApplicantExport.UserID = @PassedUserID AND
	  cast(tblApplicantExport.LastDownloadDate AS varchar(50)) is not null
	ORDER BY
	  (CASE 
	    WHEN @SortBy = 'DateDESC' THEN ' tblApplicantExport.LastDownloadDate DESC'
	    WHEN @SortBy = 'DateASC' THEN ' tblApplicantExport.LastDownloadDate ASC'
	    WHEN @SortBy = 'FirstNameDESC' THEN ' tblApplicants.FirstName DESC'
	    WHEN @SortBy = 'FirstNameASC' THEN 'tblApplicants.FirstName ASC'
	    WHEN @SortBy = 'LastNameDESC' THEN ' tblApplicants.LastName DESC'
	    WHEN @SortBy = 'LastNameASC' THEN ' tblApplicants.LastName ASC'
	  END )+@Order

The problem with this is that the ORDER BY can not have a field name as sting. So I tried to make this a dynamic statement and then execute it, but the the parts of the select that are HTML (in ' ') are not passed as strings (It looks like


'SELECT <tr><td width=5% align=center><input type=checkbox name="chkPerson" value="'+
	  cast(tblApplicantExport.UserID AS varchar)...

Please help me out if you can. I have been struggling over this for the past 4 days and trying everything I could possibly think of... thanks!

P.S. If you need more info, please ask. I am willing to give anything to get this to work...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-19 : 13:12:30

well you have two options.
1. have all single quotes in HTML turned into double (") quotes. it also looks nice if they are not mixed in a single page.
2. change all ' to '' in sql

try this:
declare @blah varchar(50)
set @blah = 'gs''g''a'
select @blah

I prefer 1.

when i reread your question it seems to me that all you need to do is put the select ... into a variable and execute that.
declare @stmt varchar(8000)
set @stmt = 'your select ...'
exec (@stmt)

or am i completly missing the point??

Go with the flow & have fun! Else fight the flow :)

Kristen
Test

22859 Posts

Posted - 2004-08-19 : 13:26:15

You need to double-up the embedded single quotes to make it dynamic. So you would have:


DECLARE @strSQL varchar(8000)
SELECT @strSQL = 'SELECT 
	  ''<tr><td width=5% align=center>...
EXEC (@strSQL)

However, if you make it dynamic you will lose most of the performance advantage you have seen so far.

You could parameterise it and use sp_executesql - that would mean that each variation of the "template" would be individually cached, but assuming that there were only three of four variations (i.e. for just the ORDER BY clause) then the frequently used ones would be in the cache and performance would be fine.

Also, dynamic SQL will mean that the user will have to have SELECT permissions on the tables - that is a significant security risk which you could remove when moving from ASP + Dynamic SQL to ASP + Stored procedures (I know that I would want that advantage, for example!!)

You could use separate stored procedures for each sort order - that way each will have its own query plan (a stored procedure is given its query plan the first time it is run [afer SQL restarts, or the procedure is created, or recompiled, or SQL chucks it out of the cache]. If THAT SPECIFIC execution has a particular Sort Order and Where Clause parameters then THAT is the stored plan that will be used for future executions - if the first execution is the one-in-million-maverick then all subsequent uses will use that query plan. So variable logic is better handled by multiple stored procedures - the first SProc can decide which of the "child" procedures to call.

Another plan might be to have your HTML as a "template" with some markers in it - so something like:


SELECT REPLACE(REPLACE(REPLACE(
'<tr><td width=5% align=center><input type=checkbox name="chkPerson" value="[tblApplicantExport.UserID]
/[tblApplicantExport.PersonID]/[tblApplicantExport.FormSetID]'
, '[tblApplicantExport.UserID]', cast(tblApplicantExport.UserID AS varchar)),
, '[tblApplicantExport.PersonID]', cast(tblApplicantExport.PersonID AS varchar)),
, '[tblApplicantExport.FormSetID]', cast(tblApplicantExport.FormSetID AS varchar))
FROM ...

which makes the whole string handling a little easier (plus you could place the actual template ina table and knock up a simple little TextArea tool to allow them to be edited within the application, rather than having to rebuild the SProc each time you change the HTML.

Kristen

hsteckylf
Starting Member

5 Posts

Posted - 2004-08-19 : 13:30:42

This is was I want to do:


	SELECT @str1 = '<tr><td width=5% align=center><input type=checkbox name="chkPerson" value="'
	SELECT @str2 = '/'
	SELECT @str3 = '"></td><td align=left width=70>'
	SELECT @str4 = '</td><td align=left width=60> '
	SELECT @str5 = '</td><td align=left>(downloaded by '
	SELECT @str6 = ' on '
	SELECT @str7 = ')</td><td width=15% align=left><input type="hidden" name="hidEvent'
	SELECT @str8 = '" value="'
	SELECT @str9 = '"><input type="hidden" name="hidAction'
	SELECT @str10 = '"><input type="hidden" name="hidFormset'
	SELECT @str11 = '"><input type="hidden" name="hidUser'
	SELECT @str12 = '"></td></tr>'

	
	SELECT @sqlStr = 'DECLARE csr CURSOR FOR SELECT '+@str1+
	  'cast(tblInquiryExport.UserID AS varchar)'+@str2+'cast(tblInquiryExport.InquiryID AS varchar)'+@str2+'cast(tblInquiryExport.FormSetID AS varchar)'+
	  @str3 +'(ISNULL (#InquirantInfo.InqFirstName, ""))'+@str4+
	  '(ISNULL (#InquirantInfo.InqLastName, ""))'+@str5+'cast(tblUserMaster.UserName AS varchar)'+
	  @str6+'cast(tblInquiryExport.LastDownloadDate AS varchar)'+@str7+
	  'cast(tblInquiryExport.UserID AS varchar)+cast(tblInquiryExport.InquiryID AS varchar)+cast(tblInquiryExport.FormSetID AS varchar)'+
	  @str8+'cast(tblInquiryExport.EventID AS varchar)'+@str9+
	  'cast(tblInquiryExport.UserID AS varchar)+cast(tblInquiryExport.InquiryID AS varchar)+cast(tblInquiryExport.FormSetID AS varchar)'+
	  @str8+'cast(tblInquiryExport.ActionID AS varchar)'+@str10+
	  'cast(tblInquiryExport.UserID AS varchar)+cast(tblInquiryExport.InquiryID AS varchar)+cast(tblInquiryExport.FormSetID AS varchar)'+
	  @str8+'cast(tblInquiryExport.FormSetID AS varchar)'+@str11+
	  'cast(tblInquiryExport.UserID AS varchar)+cast(tblInquiryExport.InquiryID AS varchar)+cast(tblInquiryExport.FormSetID AS varchar)'+
	  @str8+'cast(tblInquiryExport.UserID AS varchar)'+@str12+
	'FROM #InquirantInfo'+
	  'INNER JOIN tblInquiryExport ON #InquirantInfo.InqInquiryID = tblInquiryExport.InquiryID'+
	  'INNER JOIN tblActions ON tblInquiryExport.ActionID = tblActions.ActionID'+
	  'INNER JOIN tblUserMaster ON tblInquiryExport.UserID = tblUserMaster.UserID'+
	'WHERE tblInquiryExport.ActionID = '+cast(@PassedActionID AS varchar)+
	  'AND tblInquiryExport.UserID = '+cast(@PassedUserID AS varchar)+
	  'AND tblInquiryExport.LastDownloadDate is not null'+
	'ORDER BY '+@theOrder

	 
	execute(@sqlStr)

but that outputs:


	(minus the #)
	SELECT #<tr><td width=5% align=center><input type=checkbox name="chkPerson" value="#cast(tblInquiryExport.UserID AS varchar)#/#cast(tblInquiryExport.InquiryID AS varchar)#/#cast(tblInquiryExport.FormSetID AS varchar)#"></td><td align=left width=70>#(ISNULL (#Inqui...

I need the places where there are #'s to be quotes of some sort so that SQL will process it as a string and not attempt to look up


	SELECT <tr><td width=5% align=center><...

in the database. In essense, I need three levels of ' or " and there are only two. Please help!

Kristen
Test

22859 Posts

Posted - 2004-08-19 : 13:38:24

You just need to double up the quotes where you want them.. It gets more and more unreadable the more nested levels you have!! (Which is why I recommended a Tempalte type solution above)

SELECT @str1 = '''<tr><td width=5% align=center><input type=checkbox name="chkPerson" value="'''

By the way, you don't want to be using a CURSOR that will kill your performance.

Kristen

hsteckylf
Starting Member

5 Posts

Posted - 2004-08-19 : 13:39:37

quote:
Originally posted by Kristen

You need to double-up the embedded single quotes to make it dynamic. So you would have:

DECLARE @strSQL varchar(8000)
SELECT @strSQL = 'SELECT
''<tr><td width=5% align=center>...
EXEC (@strSQL)

However, if you make it dynamic you will lose most of the performance advantage you have seen so far.

You could parameterise it and use sp_executesql - that would mean that each variation of the "template" would be individually cached, but assuming that there were only three of four variations (i.e. for just the ORDER BY clause) then the frequently used ones would be in the cache and performance would be fine.

Also, dynamic SQL will mean that the user will have to have SELECT permissions on the tables - that is a significant security risk which you could remove when moving from ASP + Dynamic SQL to ASP + Stored procedures (I know that I would want that advantage, for example!!)

You could use separate stored procedures for each sort order - that way each will have its own query plan (a stored procedure is given its query plan the first time it is run [afer SQL restarts, or the procedure is created, or recompiled, or SQL chucks it out of the cache]. If THAT SPECIFIC execution has a particular Sort Order and Where Clause parameters then THAT is the stored plan that will be used for future executions - if the first execution is the one-in-million-maverick then all subsequent uses will use that query plan. So variable logic is better handled by multiple stored procedures - the first SProc can decide which of the "child" procedures to call.

Another plan might be to have your HTML as a "template" with some markers in it - so something like:

SELECT REPLACE(REPLACE(REPLACE(
'<tr><td width=5% align=center><input type=checkbox name="chkPerson" value="[tblApplicantExport.UserID]
/[tblApplicantExport.PersonID]/[tblApplicantExport.FormSetID]'
, '[tblApplicantExport.UserID]', cast(tblApplicantExport.UserID AS varchar)),
, '[tblApplicantExport.PersonID]', cast(tblApplicantExport.PersonID AS varchar)),
, '[tblApplicantExport.FormSetID]', cast(tblApplicantExport.FormSetID AS varchar))
FROM ...

which makes the whole string handling a little easier (plus you could place the actual template ina table and knock up a simple little TextArea tool to allow them to be edited within the application, rather than having to rebuild the SProc each time you change the HTML.

Kristen

Creating a different sproc for each case *would* work, but there are six different cases for this procedure, and anther six cases for a very similar procedure, so I am hoping that I won't have to do that. I will try a couple other things, and if I can't get anything working, I may just break down and create the 12 sub-sprocs... thanks :)

hsteckylf
Starting Member

5 Posts

Posted - 2004-08-19 : 13:42:39

quote:
By the way, you don't want to be using a CURSOR that will kill your performance.

I need the cursor to be able to go through the records and display the information. The SELECT statment that I have there displays the HTLM for one record, and I need to go through and display all records. Unless you know another way to go through records within a sproc?

***The time using a cursor was 1/10 of the time used by simple ad-hoc SQL and ASP call, and this is all I really need...

Kristen
Test

22859 Posts

Posted - 2004-08-19 : 13:45:44

Have a look at sp_executesql then, that might help you out:


DECLARE @strSQL varchar(8000)
SELECT @strSQL = 
'SELECT A, B, C 
FROM    MyTable 
WHERE   X = @X AND Y = @Y
ORDER BY ' + @MySortColumn

EXEC sp_executesql @strSQL, N'@X varchar(10), @Y int', @X, @Y

The "@strSQL" stuff will be cached. Each variation on @MySortColumn will be separately cached (but there won't be many variations). HOWEVER (and most importantly!!) @X and @Y are parameterised so variations to those values will REUSE the query plan ...

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-19 : 13:49:18

will this do?

SELECT @sqlStr = 'DECLARE csr CURSOR FOR SELECT ''' + @str1 + other stuff + ' ORDER BY '+@theOrder + ''''

edit:
damn you guys are fast... :)) very cool

Go with the flow & have fun! Else fight the flow :)

hsteckylf
Starting Member

5 Posts

Posted - 2004-08-19 : 13:50:26

thanks... between a few of your posts (Kristen) I have got this to work. and I will try out the sp_executesql procedures... again, thank you so much!

Kristen
Test

22859 Posts

Posted - 2004-08-19 : 13:53:03

Actually, pains me to say it!, but you could use sp_executesql (parameterised in this way) DIRECT from your ASP and not bother with SProcs. Should be plenty fast.

CURSOR STUFF:

So you are doing something like:

DECLARE CURSOR ... FETCH @MyPK ... SELECT * FROM MyTable WHERE X = @MyPK ...

??

Can't you just do SELECT * FROM MyTable WHERE (get lots of records in one resultset) ??

Or is there some special processing you need to do on each row?

I would expect the CURSOR to be 10 times slower than this, possibly 100 times slower - No really, I'm serious!

Even using a LOOP will be faster than a cursor. Consider this awful code:


SELECT @X = '', 
       @intRowCount = 1

WHILE @intRowCOunt > 0
BEGIN
    SELECT TOP 1 @X = X
    FROM MyTable
    WHERE X > @X
    ORDER BY X
    SELECT @intRowCount = @@ROWCOUNT

   ... do some work with @X ...

END

Most times that is a fair bit faster than a cursor, EVEN THOUGH it has to re-query the database on each and every iteration. (OK, so for a slow complex query you first of all select the "good stuff" into a temporary table, and go from there, but even so it is well know around these part that Cursors Stink!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources