[dba-SQLServer]Stored Procedures and Arrays?

Well, usual things like recompile (which you mentioned), security
(having to explicitly grant select permissions to all underlying
tables), and sql injection.

Robert Djabarov
Sr. SQL Server DBA
Phone: (210) 913-3148
Pager: (210) 753-3148


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Martin
Reid
Sent: Wednesday, August 13, 2003 2:56 AM
To: dba-sqlserver
Subject: [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

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com