Dynamic SQL

[tim]

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

 

[Garth Wells]

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

 

[Garth Wells]

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