I already posted this in the SQL Server section but now I realize it may be better suited for the SQL Section. So here it is again... 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

You can do what you want by using dynamic SQL, that is, building a string containing the desired SQL SELECT statement, then EXECuting it. (Basically, what you did in ASP).

There are issues with dynamic SQL. See http://p2p.wrox.com/topic.asp?TOPIC_ID=4534 for some of them.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com