Using a Stored Procedure to create HTML: OPTION tags for a SELECT box

[seasonedgeek]

A SQL Stored Procedure can be used to create HTML Markup, particularly an OPTION tag set for a SELECT (dropdown list) box. The OPTION VALUE attribute can be programmed to contain a single character or a character delimited string depending on how the data will be used.

An example:

SELECT '<OPTION VALUE="' + locationID + '">' +
locationName + '</OPTION>' AS opt
FROM someTable
ORDER BY locationName, locationID

Note: The resulting rows of "opt" will be truncated if length exceeds 255 chars.

Cheers!

 

[netw3rker]

thats great! but doesnt that kind of limit the effectiveness of the stored procedure? for example a developer might want to use the stored procedure:

getRelatedBooks(bookID)

to get the possible related books to the one being viewed, however by forcing the 'option' tags around the data, it illiminates the ability to say... output them as radio buttons. code maintenence becomes an issue then becuase you wind up creating:

getRelatedBooksAsOptions(bookID)
getRelatedBooksAsRadios(bookID)

then what happens when all the developer wants to do is output them as a table? a new procedure needs to be created. at point do you say there are too many slightly different versions of the same code? and, similarly, what happens when the DBA (architecht, not admin) changes the base tables w/o notifying anyone (we know it happens all the time). now you have to change several SP's instead of just 1 in order to get your code working again.

possible extensions to your idea could include:

getRelatedBooks(outputType, bookid)
where outputType is a number representing a possible format to use

or

getRelatedBooks(bookID, outputEquation)
where outputEquation would be evaluated by the SP and the onus would be on the developer to ensure that it is correct.

though potentially the best solution is:

getRelatedBooks(bookID)

and let the developer decide how the output is to be coded, in order to ensure maximum compatibility and reusability.

Just a thought
-Chris