Stored Procedure with SELECT statement having variable column name 1

[Kleptican]

Hey all,

Basically, I have a table called Rates_New, and the column headers are like PartTime_SomeNumber_D, FullTime_SomeNumber_D, etc.... (with somenumber being anywhere between 01 and 99). Basically, I find out what the number is, whether or not they are fulltime or parttime, then send a variable to be stored into @check_index_d.


This is the code for my stored procedure:

CREATE PROCEDURE dbo.getEmpOnLeave @check_index_D varchar(14), @medical varchar(2)
AS
SELECT Rate=@check_index_D, Description
FROM Rates_New
WHERE ID = @medical

And this is how I am calling it from coldfusion:

<cfstoredproc procedure="getEmpOnLeave" datasource="#Datasource#">
	<cfif EmpInfo.GHRFTPT EQ 'P'>
	        <cfset deduction = "PartTime_" & #EmpInfo.GHRPT# & "_D">
		<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="check_index_D" value="#deduction#" null="No">
	<cfelse>
		<cfset deduction = "FullTime_" & #EmpInfo.GHRPT# & "_D">
		<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="check_index_D" value="#deduction#" null="No">
	</cfif>
	<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="medical" value="#EmpInfo.Medical#" null="No">
	<cfprocresult name="EmpOnLeaveMedical" resultset="1">
</cfstoredproc>

When I do a cfdump, I get:

RATE             DESCRIPTION
FullTime_57_D    HMO : Employee Only

What I should get is some numeric value instead of the variable itself, in the rate column. My question, is when I'm using a variable in the select statement, how do I return just the selected value instead of the value I sent up?

Thanks.

 

[philhege]

Your select statement is wrong. You're telling SQL to return what you passed it. If you expect the query to use @check_index_D as the column to return, you'll need dynamic SQL:


CREATE PROCEDURE dbo.getEmpOnLeave @check_index_D varchar(14), @medical varchar(2)
AS
declare @sql varchar(512)
set @sql =
'SELECT '+@check_index_D+' as rate, Description
FROM Rates_New
WHERE ID = '''+@medical+''''

exec(@sql)

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[philhege]

I'd also lobby for a database design change. This table isn't normalized. It's not good practice to put multiple instances of the same element type (in this case, rates) across a table structure. What if you need 101 rates? You'd have to alter the table structure.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[Kleptican]

Thank you for your help philege, that worked great.