Hi,

I have a db table that is used as a mapping to pull data out of another data
table.  I basically need to create a dynamic sql based on the data from the
table (field names).  Is the best way is to use loop through the datatable
and build the string?  Any other ideas?

Thanks,

Darin

Will this approach work...


-----
SET NOCOUNT ON

CREATE TABLE ColList
(
CL_ID int PRIMARY KEY,
CL_TableName sysname NOT NULL,
CL_Col sysname NOT NULL
)
go
INSERT ColList VALUES (1,'ColList','CL_ID')
INSERT ColList VALUES (2,'ColList','CL_Col')
INSERT ColList VALUES (3,'ColLost2','CL_Col')
go

DECLARE @SQL varchar(1000),
        @ColList varchar(1000)

SELECT @ColList = COALESCE(@ColList + ', ', '') +  CL_Col
FROM ColList
WHERE CL_TableName = 'ColList'

SET @SQL =  'SELECT ' + @ColList + ' FROM ColList'
EXEC(@SQL)SET NOCOUNT ON

CREATE TABLE ColList
(
CL_ID int PRIMARY KEY,
CL_TableName sysname NOT NULL,
CL_Col sysname NOT NULL
)
go
INSERT ColList VALUES (1,'ColList','CL_ID')
INSERT ColList VALUES (2,'ColList','CL_Col')
INSERT ColList VALUES (3,'ColLost2','CL_Col')
go

DECLARE @SQL varchar(1000),
        @ColList varchar(1000)

SELECT @ColList = COALESCE(@ColList + ', ', '') +  CL_Col
FROM ColList
WHERE CL_TableName = 'ColList'
SELECT @ColList

SET @SQL =  'SELECT ' + @ColList + ' FROM ColList'
EXEC(@SQL)


-- Results --

CL_ID       CL_Col
----------- -----------
1           CL_ID
2           CL_Col
3           CL_Col




Show quoteHide quote

Let me try that again...

---
SET NOCOUNT ON

CREATE TABLE ColList
(
CL_ID int PRIMARY KEY,
CL_TableName sysname NOT NULL,
CL_Col sysname NOT NULL
)
go
INSERT ColList VALUES (1,'ColList','CL_ID')
INSERT ColList VALUES (2,'ColList','CL_Col')
INSERT ColList VALUES (3,'ColLost2','CL_Col')
go

DECLARE @SQL varchar(1000), @ColList varchar(1000)

SELECT @ColList = COALESCE(@ColList + ', ', '') +  CL_Col
FROM ColList
WHERE CL_TableName = 'ColList'
SELECT @ColList

SET @SQL =  'SELECT ' + @ColList + ' FROM ColList'
EXEC(@SQL)

-- Results --

CL_ID       CL_Col
----------- ---------
1           CL_ID
2           CL_Col
3           CL_Col

Shot in the Dark
Comparing DefaultValue to DataRow column values
Updating multiple records
Timeout in Transaction: Get two times the same data from table ada
Check to see DataColumn DataType is Boolean?