Passing variables into the SELECT clause? I want to be able to dynamically select which field to pull from the table. Here is the code:


CREATE proc select_student_flight_hotel_prices
@passengerID int ,
@flightID int ,
@hotelID int ,
@fldPriceNumber varchar(15)
As

Select (f.fldAirOnlyPrice - f.fldDiscwHotel) As FlightPrice,
@fldPriceNumber As HotelPrice , f.fldAirportTax, hr.fldpartyExtravaganza
FROM tblFlights f, tblpassengers p, tblHotelRooms hr
WHERE f.fldFlightID=@FlightID AND f.fldflightID = p.fldflightID
 AND p.fldhotelRoomID = hr.fldhotelRoomID AND hr.fldHotelRoomID=@HotelID
AND p.fldPassengerID =@passengerID

GO



ASP code which executes fine...

rs.Open "Exec select_student_flight_hotel_prices " &passengerID&","&FlightID&","& HotelID&",'"&fldPriceNumber&"' ", conn

Note: @fldpriceNumber is a string which contains the text for a field name in a table
So I am really submitting…
rs.Open "Exec select_student_flight_hotel_prices " 1, 212, 57, ‘fldPrice3’, conn

You’ll notice that fldPriceNumber is a variable in the SELECT clause. The query executes fine all the correct values are returned except the rs(“HotelPrice”) is equal to the name of the field that I enter for @fldPriceNumber (so it returns ‘fldPrice3’) and not the actual value contained in the table. So the HotelPrice should return a numeric value but it is only returning the text value that I submitted it as in the parameter. Hope that make sense and hope you have a second to look at it.

Thanks,
Jon