Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

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

 
Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc