°ïÎÒ¿´¿´ÏÂÃæµÄsqlÓï¾ä - MS-SQL Server / »ù´¡Àà

GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Test] 
@sort_expression varchar(255)='Id',
@ascending varchar(20) = 'false'
AS
BEGIN
WITH
  Found AS
(
  select * from tablename
  ),
   
Rows AS
(
SELECT ROW_NUMBER() OVER 
(
ORDER BY CASE WHEN @ascending = 'true' THEN 
CASE @sort_expression
WHEN 'Id' THEN r.id  
WHEN 'Name' THEN r.name 
--WHEN 'Type' THEN r.type 
WHEN 'ExpiryDate'THEN r.expiry_date  
END
END ASC,
CASE WHEN @ascending = 'false' THEN 
CASE @sort_expression
WHEN 'Id' THEN r.id  
WHEN 'Name' THEN r.name 
--WHEN 'Type' THEN r.type 
WHEN 'ExpiryDate'THEN r.expiry_date  
END
END DESC

) AS row_number, r.* from Found r
)

SELECT * from Rows
END

µ±ÎÒÔËÐÐ
exec Test 'id','true'
³ö´í
Error converting data type varchar to float.
Ó¦¸ÃÊÇÔÚ WHEN 'Name' THEN r.name ÕâÀï
name×Ö¶ÎÀàÐÍÊÇnvarchar
ÇëÎÊÔõô¸Ä

---

SQL code:
CASE @sort_expression
WHEN 'Id' THEN rtrim(r.id)
WHEN 'Name'