I have SQL Server stored procedure:
Select
A.Id, A.Title, A.BriefText,
I.ArticleView, U.DisplayName AS Author,
U.Avatar,
(SELECT COUNT(*) FROM Articles_Like WHERE ArticleId = A.Id) AS Likes,
(SELECT COUNT(*) FROM Articles_Comment WHERE ArticleId = A.Id) AS Comments,
CASE
WHEN EXISTS (SELECT Id FROM Articles_Like
WHERE ArticleId = A.Id AND UserId = @UserId)
THEN 1
ELSE 0
END AS IsLiked,
CASE
WHEN EXISTS (SELECT Id FROM Articles_Bookmark
WHERE ArticleId = A.Id AND UserId = @UserId)
THEN 1
ELSE 0
END AS IsBookmarked
FROM
Articles A
LEFT OUTER JOIN
Articles_Info I ON I.ArticleId = A.Id
LEFT OUTER JOIN
Users_Info U ON U.UserId = A.UserId
GROUP BY
A.Title, A.BriefText, A.Id, I.ArticleView,
U.DisplayName, U.Avatar, U.UserId
ORDER BY
CASE WHEN @orderby = 1 THEN A.Id END DESC,
CASE WHEN @orderby = 2 THEN I.ArticleView END DESC,
END DESC
CASE WHEN @orderby = 3 THEN Likes END DESC
On the last line
CASE WHEN @orderby = 3 THEN Likes END DESC
I get an error:
Invalid column name
when I use CASE aliases is not working anymore, how can I fix this?
I can use:
CASE WHEN @orderby = 3 THEN (SELECT COUNT(*) FROM Articles_Like WHERE ArticleId = A.Id) END DESC
Like this:
Select A.Id, A.Title, A.BriefText, I.ArticleView, U.DisplayName AS Author, U.Avatar,
(SELECT COUNT(*) FROM Articles_Like WHERE ArticleId=A.Id) AS Likes,
(SELECT COUNT(*) FROM Articles_Comment WHERE ArticleId=A.Id) AS Comments,
CASE WHEN EXISTS (SELECT Id FROM Articles_Like WHERE ArticleId=A.Id AND UserId=@UserId)
THEN 1 ELSE 0 END AS IsLiked,
CASE WHEN EXISTS (SELECT Id FROM Articles_Bookmark WHERE ArticleId=A.Id AND UserId=@UserId)
THEN 1 ELSE 0 END AS IsBookmarked
FROM Articles A
LEFT OUTER JOIN Articles_Info I ON I.ArticleId = A.Id
LEFT OUTER JOIN Users_Info U ON U.UserId = A.UserId
GROUP BY A.Title, A.BriefText, A.Id, I.ArticleView, U.DisplayName, U.Avatar, U.UserId
ORDER BY
CASE WHEN @orderby=1 THEN A.Id END DESC,
CASE WHEN @orderby=2 THEN I.ArticleView END DESC,
CASE WHEN @orderby=3 THEN (SELECT COUNT(*) FROM Articles_Like WHERE ArticleId=A.Id) END DESC
and it works but I used this code at the top of the stored precedure and it's not a right way to use a code twice.
I'm using SQL Server 2022
From the sql server docs
If the ORDER BY clause references a column alias from the select list, the column alias must be used on its own, and not as a part of some expression in ORDER BY clause, for example:
Copy
SELECT SCHEMA_NAME(schema_id) AS SchemaName
FROM sys.objects
ORDER BY SchemaName; -- correct
SELECT SCHEMA_NAME(schema_id) AS SchemaName
FROM sys.objects
ORDER BY SchemaName + ''; -- wrong
You can see the same situation you are encountering with the following working/non-working example:
CREATE TABLE test (id int, f1 varchar(20));
INSERT INTO test VALUES (1, 'foo');
INSERT INTO test VALUES (2, 'bar');
/*runs fine*/
SELECT id, (SELECT count(*) FROM test) as total_count
FROM test
ORDER BY total_count
/*errors*/
SELECT id, (SELECT count(*) FROM test) as total_count
FROM test
ORDER BY CASE WHEN 1=1 THEN total_count END
dbfiddle here of this in action.
To avoid code duplication, consider tossing the query without the ORDER BY into a subquery and then ordering in the main query:
SELECT *
FROM
(
Select A.Id, A.Title, A.BriefText, I.ArticleView, U.DisplayName AS Author, U.Avatar,
(SELECT COUNT(*) FROM Articles_Like WHERE ArticleId=A.Id) AS Likes,
(SELECT COUNT(*) FROM Articles_Comment WHERE ArticleId=A.Id) AS Comments,
CASE WHEN EXISTS (SELECT Id FROM Articles_Like WHERE ArticleId=A.Id AND UserId=@UserId)
THEN 1 ELSE 0 END AS IsLiked,
CASE WHEN EXISTS (SELECT Id FROM Articles_Bookmark WHERE ArticleId=A.Id AND UserId=@UserId)
THEN 1 ELSE 0 END AS IsBookmarked
FROM Articles A
LEFT OUTER JOIN Articles_Info I ON I.ArticleId = A.Id
LEFT OUTER JOIN Users_Info U ON U.UserId = A.UserId
GROUP BY A.Title, A.BriefText, A.Id, I.ArticleView, U.DisplayName, U.Avatar, U.UserId
) dt
ORDER BY
CASE WHEN @orderby=1 THEN Id END DESC,
CASE WHEN @orderby=2 THEN ArticleView END DESC,
CASE WHEN @orderby=3 THEN Likes END DESC