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.
| Author |
Topic |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2004-10-14 : 13:40:04
|
| Hi,I'm brainstorming for ideas about adding some functionality to our product and was curious if anyone had any ideas (can't hurt to ask).We have a web based application that contains a number of different screens all containing a number of different input fields. This information is stored in a number of different tables. Some of the fields are required and need input before proceeding to the next page. (That is easy enough.) Some fields are not required to complete the process, BUT if you want to perform some piece of functionality, a number of those fields now need to contain data. Essentially I need a way to check for required fields at the very end of the process. My first approach was more front-end based. I would scan each page before submittal, recording whether each input field was populated. I stored this info in memory and proceed it if necessary. Well due to the dynamic nature of the application this is no longer feasible. I need to develop a more robust way to retrieve the "required" field info from the database (after the fact, a more back-end approach). I was thinking of having a metadata table that contains the table and fieldName of each "required" item. I would then use this info to build a sql query inorder to see if that field contains any data. I think this approach will work but I'm concerned on how to do it in an efficient manner. I can take each item as a seperate sql query, but many of the items are stored in the same table, so it would make sense to hit the table only once. (another little caviat is rhe "required" status of some field is dependent whether other fields are populated. I'll need to set up a rule set for this, but I want to get the basic structure down first)I realize I might not get any responses, but I'm just curious if anyone has done anything like this and if they have any suggestions.Nic |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-14 : 14:43:46
|
| What about using your Meta Data to generate the SQL (e.g. for a stored procedure) that is run to evaluate if the data is "complete". Whenever the metadata changes (I assume infrequently) that will need to cause the SQL to be regnerated (or the process to be flagged as "stale" so that it gets regerneated "just in time" before the next envocation). This should not have any performance implications, compared to dynamic SQL.We have plenty of "just in time" execution on our systems - which re-generate SProcs based on metadata, once it is flagged as Stale, taking into account client's specific business rules and the like.Kristen |
 |
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2004-10-15 : 16:40:55
|
| good idea. I started creating a procedure which creates a procedure using the metaData (the second (static) procedure will be called from my application). The first procedure creates a procedure using dynamic sql. The problem is the final output (stand-alone procedure) is going to be well over the 8000 character limit of a varchar. How can I dynamically create a t-sql statement when the statement itself will be well over 8000 characters.Nic |
|
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2004-10-15 : 17:13:12
|
| Nevermind, looks like the exec statement can contain a concatenation of multiple strings:ie. exec(@sql + @sql1 + @sql2) as long as each item is under the 8000 character limit.Nic |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-16 : 01:39:26
|
 Darn it! You sniped me in another thread that I got to earlier ...Kristen |
|
|
|
|
|
|
|
|
brainstorming on audit field requirement