July 8, 2003 at 4:24 pm
I have two tables one is a lookup for the later. The first table contains a column called 'columnname' this column contains the names of all of the columns in the second table. There is another column in the first table which holds a bit value indicating wheter the corresponding field is enabled or not. 2 Questions for the SQL Masters... First, how would I write a query that returns the actual field values found in the second table but only for the fields that are marked as enabled in my lookup table? Second, Is this bad design? Is there a better way to acomplish this?
July 8, 2003 at 11:37 pm
Different horses for different courses.
In the run off the mill databases one do not see such level of dynamics very often.
If you do not switch columns on/off to often I think a view may be a better option but the application / user of the results must be aware of the horizontal elasticity of the rowsets. You can redefine a view every time you switch a column on/off.
To answer the second question:
Set NoCount on
Declare @C varchar(8000)
Select @C='Select '
Select @C=@c+ColumnName+',' from Table1 Where ColumnIsEnabled Ordered by ColumnName
Select @C=Left(@c,DataLength(@c)-1)+' From Table2 Where PrimaryKey=123'
Set NoCount Off
Exec (@c)
Can also use sp_executesql, see SQL Server Books Online
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply