I have a SQL Database, which has a basic 'Dynamic' database section for our users. I need a SQL Statement/UDF/Cursor combination thereof to Select the fixed field data and the dynamic field data.
This probably does not make sense, I hope a table structure may make some sense:
_tblDatabase_ *--Stores info about the clients 'database'
*DBID (PK) (FK)
ClientID (FK) --Related to Client Table
_tblRecords _ *--Stores the records in 'fixed fields' for databases in tblDatabase
DBID (FK) -- Related to tblDatabase
_tblCustomFields_ *-- Where 'CustomFields' are declared*
DBID (FK) --Related to tblDatabase
_tblCustomData_ *-- Where the 'Custom Data' Is stored (all fields have a consistant datatype)*
RecordID (FK) -- Related tblRecords
FieldID (FK) --Related to tblCustomFields
I need to use a SELECT statement to obtain (for a specifed DBID) the database contents. e.g.:
if, for the sake of example the specified database has two custom fields specified (Sex and DateOfBirth) I would want a table returned as follows:
I would preferably want this done in a UDF that returns a Table so I could easily re-use the code
1) Complete and fully-functional working Stored Procedures, User Defined Functions, Cursors including well commented code
2) Installation package - Not Applicable
3) Exclusive and complete copyrights to all work purchased. (No GPL, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site).
MS SQL Server 2000
(running on Win2k)