Passing Column Name as Parameter (SPROC)

Hello, I don't know a lot of T-SQL. I have a table with 49 columns and 48 rows (US States). The fist column is a list of "from" states and the other columns are named for each state. The point is to look up a variable column for a variable row to obtain a state to state transit rate. I tried everything I could find and no luck. If someone can help I would appreciate it, and if not thanks anyway. Gregg

You could use dynamic SQL from this problem:

Code Snippet

create table rates

(

from_state char(2),

WA decimal,

NY decimal,

CA decimal

)

insert into rates values('WA',0,1,10)

insert into rates values('NY',2,0,20)

insert into rates values('CA',3,30,0)

create procedure GetRate

@.from_state char(2),

@.to_state char(2)

AS

BEGIN

declare @.query varchar(100)

set @.query = 'select '+@.to_state+' from rates where from_state='''+@.from_state+''''

execute ( @.query)

END

But this method has SQL Injections.

Partly you could solve this problem by following code:

Code Snippet

create procedure GetRate2

@.from_state char(2),

@.to_state char(2)

AS

BEGIN

declare @.query nvarchar(100)

set @.query = 'select '+@.to_state+' from rates where from_state=@.from_state'

EXEC sp_executesql @.query, N'@.from_state char(2)', @.from_state=@.from_state

END

But best solution is convert you table for following format:

Code Snippet

create table rates2

(

from_state char(2),

to_state char(2),

rate decimal

)

|||

Here the sample ..

Code Snippet

--Sample table (first 10 States)

Create Table #statesfare (

[Starting From] Varchar(100) ,

[Alabama] Varchar(100) ,

[Alaska] Varchar(100) ,

[Arizona] Varchar(100) ,

[Arkansas] Varchar(100) ,

[California] Varchar(100) ,

[Colorado] Varchar(100) ,

[Connecticut] Varchar(100) ,

[Delaware] Varchar(100) ,

[Florida] Varchar(100) ,

[Georgia] Varchar(100)

);

--Random Generated data (Not For Scale, But City From & To same then ZERO)

Insert Into #statesfare Values('Alabama','0','21','29','90','82','65','72','84','4','51');

Insert Into #statesfare Values('Alaska','33','0','17','80','37','92','90','21','12','20');

Insert Into #statesfare Values('Arizona','62','19','0','97','23','66','22','43','94','60');

Insert Into #statesfare Values('Arkansas','61','38','12','0','98','42','68','70','81','87');

Insert Into #statesfare Values('California','92','27','82','72','0','43','84','39','24','80');

Insert Into #statesfare Values('Colorado','72','34','97','52','52','0','10','38','64','40');

Insert Into #statesfare Values('Connecticut','100','78','27','18','74','3','0','67','26','48');

Insert Into #statesfare Values('Delaware','93','49','20','88','45','3','60','0','57','77');

Insert Into #statesfare Values('Florida','79','80','37','12','90','30','24','48','0','50');

Insert Into #statesfare Values('Georgia','33','46','16','30','46','72','42','85','18','0');

Declare @.StartFrom as varchar(100);

Declare @.EndAt as varchar(100);

--Sample Input

Set @.StartFrom = 'Alabama'

Set @.EndAt = 'Georgia'

--Using Dynamic SQL (Supports Both SQL Server 2000 & 2005)

Exec ('Select ' + @.EndAt + ' Fare From #statesfare Where [Starting From] =''' +@.StartFrom + '''');

--Using UNPIVOT operator Only on SQL Server 2005

Select

[Starting From]

,[Target States]

,Fares

From

#statesfare P

UNPIVOT

(

Fares FOR [Target States] IN

(

[Alabama],

[Alaska],

[Arizona],

[Arkansas],

[California],

[Colorado],

[Connecticut],

[Delaware],

[Florida],

[Georgia]

)

) as UPVT

Where

[Starting From] = @.StartFrom

And [Target States] = @.EndAt

|||

I recommend to use quotename() function to avoid sql injection.

Code Snippet

set @.qeury = 'select ' + quotename(@.to_sate) + ' from ....';

quotename('abc') returns '[abc]';

Regards,

|||This is my table

FROM AL AR AZ CA AL 1.75 1.95 1.10 1.75 AR 1.50 1.50 1.50 1.75 AZ 1.50 1.50 1.50 1.75 CA 1.50 1.50 1.50 1.75

I Need to use a stored procedure to select one of these decimal rates based on two input parameters (@.fromstate AND @.tostate) and return the rate as an out put parameter(@.rate). I tried this even though I knew it was too simple to worrk.

ALTER PROCEDURE RC_Get_Rate

(

@.origstate nvarchar(255),

@.deststate nvarchar(255),

@.rate nvarchar(255) OUTPUT

)

AS

SET NOCOUNT ON

BEGIN

SELECT @.rate = @.deststate

FROM Rates48

WHERE Origin_State = @.origstate

END

RETURN

I'm sorry if you're previous posts already answered this, I thought I might not have been clear enougn as to my situation and my goal. I really did search a lot on this topic but got nowhere. Again if you can help (or already did) thanks, and if you can't thanks anyway, Gregg|||

here you go...

Code Snippet

Create Table rates48 (

[Origin_State] Varchar(100) ,

[AL] float ,

[AR] float ,

[AZ] float ,

[CA] float

);

Go

Insert Into rates48 Values('AL','1.75','1.95','1.10','1.75');

Insert Into rates48 Values('AR','1.50','1.50','1.50','1.75');

Insert Into rates48 Values('AZ','1.50','1.50','1.50','1.75');

Insert Into rates48 Values('CA','1.50','1.50','1.50','1.75');

Go

Create PROCEDURE RC_Get_Rate

(

@.origstate nvarchar(255),

@.deststate nvarchar(255),

@.rate nvarchar(255) OUTPUT

)

AS

SET NOCOUNT ON

BEGIN

Declare @.Query as NVarchar(1000);

Set @.Query = N'SELECT @.rateout = ' + quotename(@.deststate) + '

FROM Rates48

WHERE Origin_State = @.origstatein';

Exec sp_executesql @.Query, N'@.origstatein varchar(255), @.rateout nvarchar(255) output',@.origstatein = @.origstate, @.rateout=@.rate OUTPUT;

Return;

END

Go

Declare @.rate nvarchar(255)

Exec RC_Get_Rate 'AL', 'AZ', @.rate OUTPUT

Select @.rate

|||Thank You very much Manivannan.D.Sekaran. That works very well. I must admit though I don't know how. Could you maybe recommend a good book I could pick up to further myself on T-SQL. I would like to learn SPROC's, Functions, and some more advanced SQL. Well, again, thank you very much Manivannan.D.Sekaran, and also thank you to everyone who took the time to read my post and help me out. I really appreciate it. Thanks, Gregg