Selecting dyamic fields using meta data table

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting dyamic fields using meta data table

Author

Topic

ltgrady
Starting Member

9 Posts

Posted - 2007-10-11 : 13:25:11

I have a Product table with over 400 fields. We also have a FieldDictionary meta-data table that has a record for each of those fields that defines the Field Description, Name, the database Field Name, data type, etc.. Then we have header/detail tables called FieldGroups and FieldGroupFields. Each Field Group has 1 to many FieldGroupField records which hold the FieldGroupID and teh FieldDictionary ID. So we have logical groupings of fields for our data entry web application so users can see small chunks of data at a time (and many, many other forms adn applications).

For instance, if user selects the "Shipping" Field Group then that field group will contain FieldDictionary fields for ONLY the shipping ID's.

So, here is my problem. For searching, reporting, and web service results (especially) we don't want to have to return all 400 products. We want to set up a FieldGroup with the subset of fields we want and just return those results. Unfortunately up until this point I'm doing this by Selecting off of the meta-data tables, Coalescing the results into a comma delimited string of fields and then executing it, using dynamic SQL. Like this:

SELECT @brandFieldList = COALESCE(@brandFieldList + ',','') + CAST(FD_FieldName as varchar(30)) + ' "' + CAST(FD_FieldName as varchar(30)) + '"'
FROM tblSys_GridFieldGroupFields f
JOIN tblSys_FieldDictionary d
ON f.FD_ID=d.FD_ID
WHERE FieldGroupID=@fieldGroupID

SELECT @finalSQL = 'SELECT
tblRL_Brands.Brand_ID "@brandID",' + @brandFieldList + 'GETDATE() AS CurrentDate
FROM tblRL_Brands
WHERE [@@@Search Criteria Here@@@]
FOR XML PATH(''Brands''), ELEMENTS XSINIL, TYPE, ROOT(''xml'')''

EXEC (@finalSQL)

I really want to do this somehow without using dynamic SQL. Is there anyway to accomplish this without building this string and executing it?

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 15:31:01

I've mostly understood what you mean, but it would be much easier to grasp if you had some DDL with a few simple INSERT'd rows we could experiment on - together with "expected results" [we are obviously intended to come up with something new, but getting the existing "Result" as a Start would be comforting!]

Kristen

ltgrady
Starting Member

9 Posts

Posted - 2007-10-11 : 16:15:58

Product table has 400 fields but here are a few
Product_ID Guid
Prd_Model varchar(20)
Prd_ShortName varchar(60)
Prd_SalesCopy varchar(560)
Prd_Note varchar(255)
Prd_C_MSRP Money
Prd_C_Jobber Money
Prd_C_User Money
Prd_DropShip bit
Prd_C_DropShip Money
Prd_C_Freight Money
etc....
[There are almost 400 fields in this table]

Field Dictionary Table
FD_ID Guid
FD_FieldName varchar(50)
FD_TableName varchar(50)
FD_ShortName varchar(25)
FD_FieldDescription varchar(100)
FD_DataType varchar(20)
FD_Size varchar (20)
FD_Format varchar(20)
FD_Active bit

The fields in the first table are the records in the second table.
ie. Product_ID, Prd_Model, Prd_ShortName are all records in the FieldDictionary Table.

GridFieldGroup Table
FieldGroupID Guid
FieldGroupName varchar(20)
FieldGroupDesc varchar(100)

Core Product Fields, Shipping Fields, Warranty Fields, Pricing, Custom are all records in teh Grid Field Group Table.

GridFieldGroupField Table
FieldGroupID Guid
FD_ID Guid
FieldGroup_Sort int

This is a one to many with GridFieldGroups that holds the FieldDictionary Fields for each GridFieldGroup. For instance the Core Product Fields FieldGroup would have Prd_Name, Prd_Model, Prd_SalesCopy, and Prd_C_MSRP in it.

So a user would want to search products. They want all products with a keyword of tv in it and where MSRP price is between 200 and 300 dollars.

A normal search would be

SELECT * FROM tblRL_Products WHERE Prd_Keywords like '%tv%' AND Prd_C_MSRP Between 200 and 300.

This is going to return back say 50 products. However, for these 50 products, it will return 400 fields of data for each product returned. What we want for a report or a web service call would be those 50 records, but with only a dozen or so fields defined by the Grid Field Group.

What we are doing now is using the select in my original post to Build a dynamic SQL string with those dozen fields then concatonate the SELECT, FROM, WHERE, and SORT clauses the fields list (@brandFieldList) and then I return back XML data using FOR XML.

THe question is if there is a way to do this without using Dynamic SQL. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources