SQL - EXEC statement

[bizzydint]

I need to do a simple query retrieving the value from a particular column of the database...ie

 

SELECT myColumn

FROM myTable

WHERE user_id = 100

 

But - the "myColumn" can vary and cant be hardcoded, so i'm passing the column name as a nvarchar parameter.

 

exec('select ' + @colname + ' FROM myTable where user_id = 100')

 

But...i then need to do something with that value that gets returned. How do i get hold of it? I cant seem to declare another variable and set it when using the "exec".

 

Cheers.

van

[Nerseus]

Haven't tried this in awhile, but I think you need to use a temp table. Try something like:

CREATE TABLE #temp ([iD] int)

exec('insert into #temp select ' + @colname + ' FROM myTable where user_id = 100')

SELECT [iD] FROM #temp

 

-nerseus

[bizzydint]

perfect!

 

just had to add a DROP TABLE #temp to the end.

 

Cheers - you're a star!