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. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.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
