Stored Procedure - Order by via Case
Hej Eksperter,Jeg er fornylig gået i gang med at benytte Stored Procedures og jeg er allerede godt bidt af det. Det er virkelig en ny og behagelig verden der åbner sig, mht. system-design etc...
Desværre jeg rendt ind i et problem, som jeg håber I kan hjælpe mig videre med.
Jeg kan simpelthen ikke se mig ud af at lave en dynamisk/variabel order by vha. en case.
Når jeg tjekker syntax, for jeg fejlmeldingen: Error 207. Invalid Column name 'brand','TopReview','RRating'.
Jeg håber meget at I kan hjælpe med at få løst denne udfordring.
CREATE PROCEDURE getReviewListbyCat (
@cid int,
@OrderBy tinyint
)
AS
SET NOCOUNT ON
select distinct modelID,
(select COUNT(ModelID) FROM tbl_Reviews r9 WHERE r9.ModelID = tbl_Reviews.ModelID and ReviewActive=1) AS CountModelID,
(select DISTINCT TOP 1 ReviewDate FROM tbl_Reviews r9 WHERE r9.ModelID = tbl_Reviews.ModelID order by ReviewDate desc)as TopReview,
(select Username FROM members WHERE members.MemberID = (select TOP 1 memberID FROM tbl_Reviews r9 WHERE r9.ModelID = tbl_Reviews.ModelID order by ReviewDate desc))as TopMembername,
(select AVG(CAST(Rating AS decimal(10, 5))) FROM tbl_Reviews r9 WHERE r9.ModelID = tbl_Reviews.ModelID and ReviewActive=1) AS RRating,
(select Manufacturer from tbl_RManufacturers where tbl_RManufacturers.ManufacturerID=tbl_Reviews.ManufacturerID)as brand,
(select ManufacturerID from tbl_RManufacturers where tbl_RManufacturers.ManufacturerID=tbl_Reviews.ManufacturerID)as brandID,
(select Model from tbl_RModels where tbl_RModels.ModelID=tbl_Reviews.ModelID)as model
from tbl_Reviews where CatID=@cid and ReviewActive=1
ORDER BY CASE @OrderBy
WHEN 1 THEN cast(brand as sql_variant)
WHEN 2 THEN cast(TopReview as sql_variant)
WHEN 3 THEN cast(RRating as sql_variant)
END
GO