MS SQL

MS SQL RSS

dynamic sql query...

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Oct 2006

Posts: 2

Reputation: sangfroid is an unknown quantity at this point

Solved Threads: 0

sangfroid

Offline

Newbie Poster

dynamic sql query...

Jun 7th, 2008

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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
declare @column_name nvarchar(10)
SET @column_name = 'original'
SELECT @column_name FROM DATA
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 ??

•

Join Date: Feb 2008

Posts: 42

Reputation: huangzhi is an unknown quantity at this point

Solved Threads: 13

huangzhi

Offline

Light Poster

Re: dynamic sql query...

Jun 9th, 2008

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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