Re: Dynamic SQL by Garth
Garth
Mon May 08 19:46:22 CDT 2006
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
"tim" <timmy@hasnoemail.com> wrote in message
news:uSSQ7CucGHA.1264@TK2MSFTNGP05.phx.gbl...
> 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
>
>