[dba-SQLServer]Stored Procedures and Arrays?

Hi All

Any comments on the following in terms of performance or anything else that
springs to mind. I have a couple of concerns over this approach re
recompiling of the SP every time it is called.

Say I want to build a drop list on the form for the user to select from and
the drop list is from a table.  Now, the application has many forms with
drop lists. Instead of having a separate SP for each drop list, we create a
generic SP that accepts the field names as a string separated by commas.  In
my code, for each form, I would define what these fields would be in an
array.  Then, with the VB join function, the array is made into a comma
separated string of strings that is passed, along with the table name, to
the generic stored procedure.  The stored procedure will take these two
parameters and convert it into a select statement, process it and return the
data for my code to process into the drop list.  Another parameter can be
passed for setting the field or fields that are used for ordering the data
(for example order by name).


Martin