select distinct on a dynamically assigned table and column

Question

select distinct on a dynamically assigned table and column

John Diss

SSC Enthusiast

Points: 156
More actions
March 14, 2006 at 4:06 am

#113231

Hi everyone, I am trying to select a distinct list of values from a dynamically assigned column/table.
The ultimate goal is to be able to dynamically build search forms (web, mobile, desktop) from a select statement in the database. I have a table which contains meta data about a given column and is related to the syscolumns table. so far it contains values for ProvideDistinct, ValidationType and a couple of others.
I am then trying to build an xml file using For XML Explicit which contains a structure similar tosearchLayerAttribute equates to a column)
<
searchLayers templateId="15">
<
searchLayer layerId="22" layerName="BLPU" layerSearchView="tbl_mySearchTable" layerAlias="Address" minScale="0" maxScale="100000000">
<
searchLayerAttribute index="2" attributeName="UPRN" inputType="varchar">
<
defaultValues>
<
defaultValue>val1</defaultValue>
<
defaultValue>val2</defaultValue>
</
defaultValues>
</
searchLayerAttribute>
<searchLayerAttribute index="3" attributeName="outcode" inputType="varchar">
<
defaultValues>
<
defaultValue>EC1</defaultValue>
<
defaultValue>W1</defaultValue>
</
defaultValues>
</
searchLayerAttribute>
</searchLayer>
.
.
.
</searchLayers>
If the meta data column says it should provide distinct I want to be able to select the distinct list into the defaultValues Tag at that point I know what the object names and column names are but cant work out how to query the actual column of the actual table or view

what I need is something like in psudo code
select distinct [syscolumns.name] from [sysobjects.name]
this obviously doesn't work but hopefully will give somebody an idea of what I am trying to acheive.
I could possibly do it with executing strings but I am sure there must be a nicer way..
Thanks jd

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Chris-164356 SSC Veteran Points: 215 More actions · Answer 1

There is no nicer way, in SQL2000 tablenames cannot be paramerized, you have to build the SQL and exec it (or sp_executesql it)

John Diss SSC Enthusiast Points: 156 More actions · Answer 2

Hi Chris, thanks for the reply.. I really thought there would be an elegant solution!! never mind I have got around it (after a fashion) using sp_executesql

step 1 create a #table following the schema that for xml explicit uses([elementName!tagdepth!attributeName])

step 2 insert into the #table all the values needed to build the 'non distinct' elements

step 3 create a table valued variable @table (with identifier column) to store the elements we need to 'get distinct' for

step 4 insert into @table all the elements we need

step 5 using a counter loop through all the @table getting params out

step 6 for each row in @table build an sql string 'insert into #table select....' and execute it

step 7 select * from #table for xml explicit...

long winded but it works...

Hope this helps somebody at some point... cheers jd