I tried putting both single and double quotes around the variable name. It compiles fine, but I don't get the results I need. The resultset is still sorted by the order in which the data was input into it's table.
-----Original Message----- From: Greg Luce [mailto:[EMAIL PROTECTED]] Sent: Monday, October 01, 2001 3:57 PM To: CF-Talk Subject: RE: SQL stored procedure errors Sounds like you might need single quotes around the variable in the proc. Greg Luce 954-763-4504 -----Original Message----- From: Brian Ferrigno [mailto:[EMAIL PROTECTED]] Sent: Monday, October 01, 2001 3:53 PM To: CF-Talk Subject: SQL stored procedure errors I am passing a variable from a CF template to be used in the ORDER BY part of SELECT statement in a stored procedure. However, after trying to add the ORDER BY statement to the stored procedure, I get the following error message: Error 1008: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name. But when I use an explicit column name (ex. ProductName) there is no error and the sp works fine. Is there a work-around for this error? And can anyone a good website or book on writing stored procedures or SQL in general? TEMPLATE PAGE <cfstoredproc procedure="sp_Test" datasource="#application.dsn#"> <cfprocparam type="In" dbvarname="@OrderBy" value="#Attributes.sort#" cfsqltype="CF_SQL_VARCHAR"> <cfprocresult name="ProductInfo"> </cfstoredproc> STORED PROCEDURE CREATE PROCEDURE sp_Test @OrderBy varchar(50) AS SELECT A.Name AS ProductName,ID_Product,PartNumber,Price,Description_Brief,Active,ImageNa me,B Name AS VendorName,B.ID_Vendor,C.Name AS Availability,Count (*) as Total FROM Products A, Vendor B, Products_Availabilty C WHERE A.ID_Vendor=B.ID_Vendor AND A.ID_Availabilty=C.ID_Availabilty GROUP BY A.Name,ID_Product,PartNumber,Price,Description_Brief,Active,ImageName,B. Name ,B.ID_Vendor,C.Name ORDER BY @OrderBy RETURN GO Thanks in advance for any help you can give. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
