dynamic sql query...

[sangfroid]

Hi
I am trying to write a function in which I customize my select statement. But it is not working as i desire...

It is as

declare @column_name nvarchar(10)
set @column_name = 'original'
select @column_name from data

Here original is the name of one of the columns. I wanted to make it such that column_name can be assigned any of the column names and the same piece of code works only by changing the value of @column_name...but it is not working....

any ideas ??

[huangzhi]

create table #tmp (Code varchar(255), Price money, Date smalldatetime)

insert #tmp (Code, Price, Date)
select 'ProductA', 12.00, '12/10/2002' union all
select 'ProductA', 12.50, '01/15/2005' union all
select 'ProductA', 12.01, '01/02/2008' union all
select 'ProductB', 15.12, '12/01/2005' union all
select 'ProductB', 16.00, '01/09/2008' union all
select 'ProductB', 16.24, '01/15/2008'

declare @column_name nvarchar(10)
set @column_name = 'Code'
exec ('select ' + @column_name + ' from #tmp')

set @column_name = 'Price'
exec ('select ' + @column_name + ' from #tmp')

drop table #tmp

[harie.in]

declare @query nvarchar(Max) ,@tbl nvarchar(Max) ,@col nvarchar(Max) ,@val nvarchar(Max) 
  -- give tbl=tablename,col=column,val=value
 set @query='select @retVal= count(*) from '+@tbl+' where '+@col+' like ''%'+@val+'%'''
 
EXECUTE sp_executesql @query, N'@retVal int OUTPUT', @retVal OUTPUT
 
 select @retVal