What is the best way to CRUD dynamically created tables?

I'm creating(ed) an ASP.NET application (SQL Server backend) that allows the user (a business) to create their own tables and fields. They will all be child tables of a parent table (non-dynamic) and have proper PK/FK relationsh开发者_StackOverflowips (default fields when the table is created).

However, I don't like my current method of updating/inserting and selecting the fields. I was going to create an SP that was passed the proper keys and table names, then have it return the proper SQL statement. I'm thinking that it might make more sense to just pass the name/value pairs of fields/values and have an SP actually process them. Is this the best way to do it? If so, I'm not good at SP's so any examples of how?

I don't have a lot of experience with the EAV model, but it does sound like it might be a good idea for implementing what you're trying to achieve. However, if you already have a system in place, an overhaul could be very expensive.

If the queries you're making against the user tables are basic CRUD operations, what about just creating CRUD stored procs for each table? E.g. -

Table:

acme_orders

Stored Procs:

acme_orders_insert
acme_orders_update
acme_orders_select
acme_orders_delete
... [other necessary procs]

I have no idea what the business needs are for these tables, but I imagine that whatever you're doing currently could be translated into doing the same thing with stored procs.

I was going to create an SP that was passed the proper keys and table names, then have it >return the proper SQL statement. I'm thinking that it might make more sense to just pass the >name/value pairs of fields/values and have an SP actually process them.

Assuming you mean the proc would generate and then execute the SQL (sometimes known as dynamic SQL) this can work, but it probably performs slower than static / compiled SQL, as in normal procs.