column filter by lookup table or user input

• dJeePe

  Say Hey Kid

  Points: 712

  More actions

  June 11, 2007 at 3:08 pm

  #176993

  I am having a problem finding a solution for this:

  --this is only an example not a real table

  Declare @t table(

  addressNum int,

  addressStreet varchar(8),

  unitNumber varchar(10),

  city varchar(20),

  state varchar(2),

  zip int

  )

  Insert into @t values(12, 'Jackson', 'apt.3D', 'Chicago', 'IL', 60607)

  Insert into @t values(115, 'Briar', 'apt. 5F', 'Chicago', 'IL', 60657)

  For certain queries I would like to filter the columns selected based on some user preferences. I.E. if some user doesn't want to see city state or zip I can perform some filtering such that I get NULL for those columns. Ideally it would work something like this

  SELECT *

  FROM (

  --some derived table which has been filtered

  ) FilteredTable

  WHERE id=@id

  if a user doesn't want to see unitNumber this query would result in

  addressNum addressSreet unitNumber city state zip

  -------------------------------------------------------------------

  12 'Jackson' NULL 'Chicago' 'IL' 60607

  115 'Briar' NULL 'Chicago' 'IL' 60657

  or a user doesn't want to see state this query would result in

  addressNum addressSreet unitNumber city state zip

  -------------------------------------------------------------------

  12 'Jackson' 'apt.3D' 'Chicago' NULL 60607

  115 'Briar' 'apt.5F' 'Chicago' NULL 60657

  Thanks

• Claudia-343424

  Ten Centuries

  Points: 1080

  More actions

  June 11, 2007 at 5:01 pm

  #712139

  How do they decide what to see and what to hide?

  You could use a temporary table for this..

  On your example let say that I don't want to see city....

   

  Select * into #tmp from

  where id=@id

  Update #tmp set city=null

  Select * from #tmp

  Drop Table #tmp

   

  this table @Tmp it will exist only inside this session... so if more that one user perform the same storage it will work fine!