帮我看看下面的sql语句

Question

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
请问怎么改

Answer 1

CASE @sort_expression
WHEN 'Id' THEN rtrim(r.id)
WHEN 'Name' THEN r.name 
--WHEN 'Type' THEN r.type 
WHEN 'ExpiryDate'THEN convert(varchar(10),r.expiry_date,120)
END

Answer 2

case when xxx  then  A
     when xx   then  B ....类似这样的语法，A,B的数据类型要求一致。如不一致，sql会自动转。可以参看online book 的数据类型

Answer 3

像 #1 那样写即可。手动将其转成 char

Answer 4

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 ltrim(r.id)   
WHEN 'Name' THEN r.name  
--WHEN 'Type' THEN r.type  
WHEN 'ExpiryDate'THEN convert(varchar,r.[expiry_date],23) 
END
END ASC,
CASE 
WHEN @ascending = 'false' THEN  
CASE @sort_expression
WHEN 'Id' THEN ltrim(r.id)   
WHEN 'Name' THEN r.name  
--WHEN 'Type' THEN r.type  
WHEN 'ExpiryDate' THEN convert(varchar,r.[expiry_date],23)   
END
END DESC ) AS [row_number], r.* 
FROM Found r 
)
SELECT * FROM [Rows]
END怀疑问题出在ID和时间字段上，但ID一但转为字符，排序就会出现问题，先运行一下这个代码看会不会出错先

Answer 5

WHEN 'Id' THEN ltrim(r.id)   
WHEN 'Name' THEN r.name  试试看

Answer 6

htl258 说的对 现在id 也是按照字母排序了,先排1开头的，再2，依次类推，怎么解决？

Answer 7

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 right(10000000+r.id,8)--看你ID最大值多少，自已看着办   
                                WHEN 'Name' THEN r.name  
                                --WHEN 'Type' THEN r.type  
                                WHEN 'ExpiryDate'THEN convert(varchar,r.[expiry_date],23) 
                            END
                END ASC,
                CASE 
                    WHEN @ascending = 'false' THEN  
                            CASE @sort_expression
                                WHEN 'Id' THEN right(10000000+r.id,8)   
                                WHEN 'Name' THEN r.name  
                                --WHEN 'Type' THEN r.type  
                                WHEN 'ExpiryDate' THEN convert(varchar,r.[expiry_date],23)   
                            END
                END DESC            ) AS [row_number], r.* 
    FROM Found r 
)
SELECT * FROM [Rows]
END

Answer 8

如果实在很大:right(replicate('0',16)+ltrim(r.id),16) --到16位

Answer 9

12楼少个0,应改为 right(100000000+r.id,8),或把它用13楼的代替

Answer 10

谢谢htl258， 我的ExpiryDate是float类型的，怎么弄呢？

Answer 11

如果有小数，这样吧：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 right(replicate('0',16)+ltrim(r.id),16) 
                                WHEN 'Name' THEN r.name  
                                --WHEN 'Type' THEN r.type  
                                WHEN 'ExpiryDate'THEN right(replicate('0',23)+convert(varchar,cast(r.[expiry_date] as dec(23,7))),23)  
                            END
                END ASC,
                CASE 
                    WHEN @ascending = 'false' THEN  
                            CASE @sort_expression
                                WHEN 'Id' THEN right(replicate('0',16)+ltrim(r.id),16)    
                                WHEN 'Name' THEN r.name  
                                --WHEN 'Type' THEN r.type  
                                WHEN 'ExpiryDate' THEN right(replicate('0',23)+convert(varchar,cast(r.[expiry_date] as dec(23,7))),23)   
                            END
                END DESC            ) AS [row_number], r.* 
    FROM Found r 
)
SELECT * FROM [Rows]
END

Answer 12

8位的话把as dec(23,7)里面的7改为8