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 |
|
jshepler
Yak Posting Veteran
60 Posts |
 Posted - 2007-03-08 : 11:47:01
|
| Hi,Given this kind of table structure:Models: ModelID int, Name varcharAdapters: AdapterID int, ModelID intItems: ItemID int, ModelID int, Name varchar, ValueFunction varcharValues: ValueID int, AdapterID int, ItemID int, Value varcharI need to write a query that will return the items and values for a specified AdapterID where if the Items' ValueFunction is not null or <> '', run that function otherwise get the value from the Values table.Something like:[CODE]SELECT i.Name, Value = CASE WHEN i.ValueFunction IS NOT NULL AND i.ValueFunction <> '' THEN ??? ELSE ISNULL(v.Value, 'n/a') ENDFROM Adapters a INNER JOIN Items i ON a.ModelID = i.ModelID LEFT JOIN Values v ON a.AdapterID = v.AdapterID AND i.ItemID = v.ItemIDWHERE a.AdapterID = @AdapterID[/CODE]I don't know what to put for ???. Can something like that even be done?I'm hoping that I don't have to build a dynamic query using UNIONs as it is very possible that the string would exceed 8000 characters. I also don't want to use a cursor because it would be too slow. There are many items per model.Any ideas?/jeff |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-08 : 13:09:33
|
| Yes you will have to use dynamic SQL if you want to do it in T-SQL. I'd suggest either using a computed column if there are just a few different expressions, otherwise if that value is truly a user entered expression that is unknown in the database, you should probably be evaluating it in the front end or application tier rather than in the SQL query. |
 |
|
|
jshepler
Yak Posting Veteran
60 Posts |
Posted - 2007-03-08 : 15:32:21
|
| It's the name of the function to call, not an expression to evaluate.The function might return a value from another table, or another database, or it returns a calculated value. The point is, it's dynamic.For example, an item might be "Password" and the value for that item is stored in the billing database and not in this configuration database. Instead of storing a copy of the password in the configuration database (for which I would have to have code in place on both databases to keep the values in sync), the ValueFunction column for the "Password" item might be "dbo.fn_GetPassword" which contains code to return the password stored in the billing database. Now, when I need to load the configuration data for a particular adapter, the Value column for the row containing the "Password" item would be populated from the result of the function instead of from the values table in the configuration database.The set of items (or model schema, or model template, or whatever you would like to call it) defined for a particular model is different than another model. The query is passed the adapter ID for which it gets the model ID and builds a recordset of the items for that adapter and the values for those items. The values for some of those items won't be in the values table and the database doesn't automatically know where to get the value - the item speicifies the function to execute.If I can do this using stored procedures instead of functions, then that's fine - nothing requires that I use functions other than being able to use functions in a select. If I was to use a stored procedure, I would probably have to build a cursor to iterate through the rows of items that define a stored procedure to run and then run them. I didn't want to use a cursor.I'm sorry if I'm not explaining this very well./jeff |
|
|
|
|
|
|
|
|
Function/SProc name in a column