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,ImageName,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
