I currently have:
****************************************
CREATE Procedure SS_POList
(
@.CompanyID nvarchar(10),
@.PO varchar (20) = '%'
)
As
SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost
FROM
SS_Sendback
WHERE
SS_Sendback.EndusercustomerNumber = @.CompanyID
AND
SS_Sendback.EnduserPO Like @.PO
GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO
ORDER BY
SS_Sendback.PO
GO
*************************************
I changed it to
*************************************
CREATE Procedure SS_POList
(
@.CompanyID nvarchar(10),
@.PO varchar (20) = '%',
@.Order varchar(20)
)
As
SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost
FROM
SS_Sendback
WHERE
SS_Sendback.EndusercustomerNumber = @.CompanyID
AND
SS_Sendback.EnduserPO Like @.PO
GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO
ORDER BY
@.Order
GO
and I receive the following error..
error1008: 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.
How does it know @.order is a position and not a column name... It's a variable. I'm obviously doing something wrong. Can someone help.
Thanksas you've discovered, the order by cannot be dynamic. you'd have to use a CASE statement to use a variable here - pseudocode
CASE @.orderBy = 'column1'
ORDER BY column1
that kinda thing. I've seen an FAQ on this somewhere, I'll see if I can dig out the link.|||I'd like to see the FAQ if you can find it. I tried looking but I had no luck.|||try this one for size :
http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=344|||Rather than the method suggested in the article:
declare @.OrderBy varchar(20)select @.Orderby = 'ContactName'
select * from Northwind.dbo.Customers
ORDER BY
CASE @.OrderBy
WHEN 'CompanyName' THEN CompanyName
WHEN 'ContactName' THEN ContactName
ENDASC
I suggest this method:
declare @.OrderBy varchar(20)select @.Orderby = 'ContactName'
select * from Northwind.dbo.Customers
ORDER BY
CASE WHEN @.OrderBy ='CompanyName' THEN CompanyName END ASC,
CASE WHEN @.OrderBy ='ContactName' THEN ContactName END ASC
The method in the article requires all of the columns to be of the same datatype (or CAST as the same datatype). The alternate method allows you to use different data types easily, plus use ASC and DESC as needed.
Terri|||Thanks for all you help.
This seems like a lot of work when a simple
ORDER BY @.somevariable
should work but apperently thats not supported by SQL server.
In my searches I also found some suggestions to assign the search string to a variable and execute the string but I think that would be the least desireable way to do this.
Thanks Again.
No comments:
Post a Comment