当前位置: 代码迷 >> Sql Server >> case出异常,
  详细解决方案

case出异常,

热度:115   发布时间:2016-04-27 20:18:40.0
case出错误,急!
--查询下面语句报错:从数据类型   nvarchar   转换为   float   时出错。

declare   @temp   table(company   nvarchar(50),account   nvarchar(50),keyword   nvarchar(50),cost   float)
insert   into   @temp(company,account,keyword,cost)   values( 'a ', 'r ', 'u ',1.0)
insert   into   @temp(company,account,keyword,cost)   values( 'b ', 't ', 'i ',2.0)
insert   into   @temp(company,account,keyword,cost)   values( 'c ', 'y ', 'o ',1.3)
declare   @dir   nvarchar(10),@para   nvarchar(50)
select   @dir= 'desc ',@para= 'company '
select   company,account,keyword,cost   from   @temp
order   by   case   @dir
when   'desc '   THEN   CASE   @para  
when   'company ' then   company
when   'account ' then   account
when   'keyword ' then   keyword
when   'cost ' then   cost
end
  end   desc,
  case   @dir  
when   'asc '   THEN   CASE   @para  
when   'company ' then   company
when   'account ' then   account
when   'keyword ' then   keyword
when   'cost ' then   cost
end
  end   ,
case   @dir
when   '0 '   then   1
end/**/

------解决方案--------------------
晕,这么处理

declare @temp table(company nvarchar(50),account nvarchar(50),keyword nvarchar(50),cost float)
insert into @temp(company,account,keyword,cost) values( 'a ', 'r ', 'u ',1.0)
insert into @temp(company,account,keyword,cost) values( 'b ', 't ', 'i ',2.0)
insert into @temp(company,account,keyword,cost) values( 'c ', 'y ', 'o ',1.3)
declare @dir nvarchar(10),@para nvarchar(50)
select @dir= 'desc ',@para= 'company '
select company,account,keyword,cost from @temp
order by case @dir
when 'desc ' THEN CASE @para
when 'company ' then company
when 'account ' then account
when 'keyword ' then keyword
else ' '
end
end desc,
case when @dir= 'desc ' and @para = 'cost ' then cost
else 0
end desc,
case @dir
when 'asc ' THEN CASE @para
when 'company ' then company
when 'account ' then account
when 'keyword ' then keyword
else ' '
end
end ,
case when @dir= 'asc ' and @para = 'cost ' then cost
else 0
end
  相关解决方案