Dynamic Column Name

Hi all,
I'm have a query which return a value from a dynamic column name .

Declare @amount numeric(25,0)
Declare @ColumnName varchar(20)
Set @ColumnName= 'SaleCol1'

SELECT @amount = SaleCol1 FROM Sale WHERE year=1998
--- is running fine

SELECT @amount = @ColumnName FROM Sale WHERE year=1998
---error: convertion from varchar to numeric
How can I fix it ?
Any advice will be very much appreciated.

On 6 7 , 10 30 , Truc H. <T@discussions.microsoft.com> wrote:

> Hi all,
> I'm have a query which return a value from a dynamic column name .

> Declare @amount numeric(25,0)
> Declare @ColumnName varchar(20)
> Set @ColumnName= 'SaleCol1'

> SELECT @amount = SaleCol1 FROM Sale WHERE year=1998
> --- is running fine

> SELECT @amount = @ColumnName FROM Sale WHERE year=1998
> ---error: convertion from varchar to numeric
> How can I fix it ?
> Any advice will be very much appreciated.

You should use dynamic sql :)

-----------------------------------------------Reply-----------------------------------------------

> Declare @amount numeric(25,0)
> Declare @ColumnName varchar(20)
> Set @ColumnName= 'SaleCol1'

> SELECT @amount = SaleCol1 FROM Sale WHERE year=1998
> --- is running fine

> SELECT @amount = @ColumnName FROM Sale WHERE year=1998
> ---error: convertion from varchar to numeric
> How can I fix it ?
> Any advice will be very much appreciated.

Shouldn't you use case ... when .. then ... else... end?

--
iTech Consulting Services Limited
Expert in ePOS (Point-Of-Sales) solutions
Website: http://www.itech.com.hk (IE only)
Tel: (852)2325 3883 Fax: (852)2325 8288

-----------------------------------------------Reply-----------------------------------------------

On Jun 7, 7:30 am, Truc H. <T@discussions.microsoft.com> wrote:

> Hi all,
> I'm have a query which return a value from a dynamic column name .

> Declare @amount numeric(25,0)
> Declare @ColumnName varchar(20)
> Set @ColumnName= 'SaleCol1'

> SELECT @amount = SaleCol1 FROM Sale WHERE year=1998
> --- is running fine

> SELECT @amount = @ColumnName FROM Sale WHERE year=1998
> ---error: convertion from varchar to numeric
> How can I fix it ?
> Any advice will be very much appreciated.

@amount need to be defined with in the scope of dynamic and you can
refer that variable will not be available after execution of the
statement

create table #sale (SaleCol1 numeric(10,0),year int )
insert into #sale values(123.45,1998)

--Declare @amount numeric(25,0)
Declare @ColumnName varchar(20)
declare @vchsql varchar(4000)
Set @ColumnName= 'SaleCol1'

set @vchsql = ' declare @amount numeric(25,0); select @amount = ' +
@ColumnName + ' FROM #sale WHERE year=1998 ; select @amount'
print @vchsql

exec (@vchsql)

-----------------------------------------------Reply-----------------------------------------------

Truc H. (T@discussions.microsoft.com) writes:
> I'm have a query which return a value from a dynamic column name .

> Declare @amount numeric(25,0)
> Declare @ColumnName varchar(20)
> Set @ColumnName= 'SaleCol1'

> SELECT @amount = SaleCol1 FROM Sale WHERE year=1998
> --- is running fine

> SELECT @amount = @ColumnName FROM Sale WHERE year=1998
> ---error: convertion from varchar to numeric
> How can I fix it ?
> Any advice will be very much appreciated.

SELECT @amount = CASE @ColumnName
WHEN 'SaleCol1' THEN SaleCol1
WHEN 'SaleCol2' THEN SaleCol2
...
END
FROM Sale WHERE year = 1998

--
Erland Sommarskog, SQL Server MVP, esq@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Sql Server Programming

Dynamic Column Name