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 |
|
WeeBubba
Starting Member
18 Posts |
 Posted - 2007-04-18 : 17:40:10
|
| hello there. i am trying to work out the best way to utilise a dynamic SQL or stored proc in order to maximise performance for a certain business requirement. i would be grateful for any advice.i am developing a database that must allow for user defined fields. to summarise, every CONTACT in my database has a CONTACT TYPE. and the UDF's are set on a per CONTACT TYPE basis.when the WinForm loads, my client wants to fill a grid with every contact and their associated UDF's. exactly what information is retrieved is based on a grid filter i.e. the user can choose which columns of information they wish to display.the main problem i have is how to join and return the UDF information in an efficient manner. this is a problem because the UDF description is a row value within the table, rather than a column header.for example, lets say the user chooses to display only the grid columns FORENAME, SURNAME and HAIRCOLOR (where HAIRCOLOR is a UDF). in this case in order to select the HAIRCOLOR alone i must be doing something like this:SELECT udfvalue FROM contacttype_udf WHERE contacttype = X and udfdescription = 'HAIRCOLOR'.this is ok when i try to run it as a standalone query, but i am having to do this for potentially a lot of UDF's and CONTACTS. any advice much appreciated. thanks. |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-04-18 : 20:28:48
|
| Microsoft put together a SDK WebApp It comes with the sourcecode (C#.net/Vb.net) along with a option to use either SQL or MSAccess.Just pick SQL and all the STored Procedures and tables will automatically install for you.I used this and found it is a good referance of using UserDefined Fields in a application and also how to setup the tables.Once you download the SDK , the tables that demenstrate this are called IssueTracker_ProjectCustomFields andIssueTracker_ProjectCustomFieldValuesHopfully this will get you started.You can download issuetracker at http://www.asp.net/StarterKits/DownloadIssueTracker.aspx?tabindex=0&tabid=1 |
 |
|
|
|
|
|
User Defined Fields Best Method