存储过程里怎么用order by 不同的条件。

luancode 2008-10-09 03:17:10
前面跟一个参数过来,用户选择的@orderby int
假设orderby的值为1,执行order by price1 desc
orderby值为2时,执行order by price1 asc
orderby 值为空时,即用户不选择条件排序,那么order by id desc

CREATE procedure ProList
@myname varchar(100),
@orderby int
as
select id,myname,price from [pro]
where online=1
and myname like '%'+@myname+'%'

这儿排序怎么写

GO
...全文
165 12 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
水族杰纶 2008-10-09
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 wufeng4552 的回复:]
SQL codeCREATE procedure ProList
@myname varchar(100),
@orderby int
as
declare @s varchar(5000)
set @s='select id,myname,price from [pro] where online=1 and myname like '''+ '%'+@myname+'%'''
if @orderby=1
exec(@s+' order by price1 desc')
else if @orderby=2
exec(@s+ ' order by price1 asc')
else
exec (@s+'order by id desc')…
[/Quote]
把你的做成動態不可以嗎
Print @S
看看有沒有語法錯誤
luancode 2008-10-09
  • 打赏
  • 举报
回复
CREATE procedure ProList
@bid varchar(20),
@sid varchar(20),
@myname varchar(100),
@myitem varchar(100),
@price_1 int,
@price_2 int,
@commend int,
@orderby int
as
select id,myname,img,price1,commend,online,hit,addtime,kucun from [pro]
where online=1
and (',')+bid like '%,'+@bid+'%'
and (',')+sid like '%,'+@sid+'%'
and myname like '%'+@myname+'%'
and myitem like '%'+@myitem+'%'
and price1>=(case @price_1 when 0 then price1 else @price_1 end)
and price2<=(case @price_2 when 0 then price2 else @price_2 end)
and commend=@commend
order by (case @orderby when 1 then price1 when 2 then id else addtime end) desc
GO

这样执行正确的,可是达不到我想要的效果,price asc.哎
水族杰纶 2008-10-09
  • 打赏
  • 举报
回复
[Quote=引用 9 楼 luancode 的回复:]
CREATE procedure ProList
@bid varchar(20),
@sid varchar(20),
@myname varchar(100),
@myitem varchar(100),
@price_1 int,
@price_2 int,
@commend int,
@orderby int
as
declare @s varchar(5000)
set @s='select id,myname,img,price1,commend,online,hit,addtime,kucun from [pro]
where online=1
and ('','')+bid like '+'%,'+@bid+'%''
and ('','')+sid like '+'%,'+@sid+'%''
and mynam…
[/Quote]
這個問題應該不是存儲過程的
你先在查詢分析器調試看看~~
luancode 2008-10-09
  • 打赏
  • 举报
回复
CREATE procedure ProList
@bid varchar(20),
@sid varchar(20),
@myname varchar(100),
@myitem varchar(100),
@price_1 int,
@price_2 int,
@commend int,
@orderby int
as
declare @s varchar(5000)
set @s='select id,myname,img,price1,commend,online,hit,addtime,kucun from [pro]
where online=1
and ('','')+bid like '+'%,'+@bid+'%''
and ('','')+sid like '+'%,'+@sid+'%''
and myname like '+'%'+@myname+'%''
and myitem like '+'%'+@myitem+'%''
and price1>=(case @price_1 when 0 then price1 else @price_1 end)
and price2<=(case @price_2 when 0 then price2 else @price_2 end)
and commend=@commend'
if @orderby=1
exec(@s+' order by price1 desc')
else if @orderby=2
exec(@s+ ' order by price1 asc')
else
exec (@s+'order by id desc')

这是我的全部代码。
这样写后,提示:不允许对具有不止一条 SELECT 语句的远程存储过程或存储过程使用服务器游标。请使用默认结果集或客户端游标。我在分页那儿调用:set rs=cmd.execute 这行提示错误。
dobear_0922,你那样写,我的代码是不是会很多?
dobear_0922 2008-10-09
  • 打赏
  • 举报
回复
CREATE procedure ProList 
@myname varchar(100),
@orderby int
as
begin
if @orderby=1
select id,myname,price from [pro]
where online=1
and myname like '%'+@myname+'%'
order by price1 desc
else if @orderby=2
select id,myname,price from [pro]
where online=1
and myname like '%'+@myname+'%'
order by price1
else
select id,myname,price from [pro]
where online=1
and myname like '%'+@myname+'%'
order by id desc
end
go
水族杰纶 2008-10-09
  • 打赏
  • 举报
回复
CREATE procedure ProList 
@myname varchar(100),
@orderby int
as
declare @s varchar(5000)
set @s='select id,myname,price from [pro] where online=1 and myname like '''+ '%'+@myname+'%'''
if @orderby=1
exec(@s+' order by price1 desc')
else if @orderby=2
exec(@s+ ' order by price1 asc')
else
exec (@s+'order by id desc')

luancode 2008-10-09
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 nalnait 的回复:]
CREATE procedure ProList
@myname varchar(100),
@orderby int
as
select id,myname,price from [pro]
where online=1
and myname like '%'+@myname+'%'

--这儿排序怎么写
order by case when @orderby is null then id else price1 end desc

GO
[/Quote]
还有一种情况,price1升序怎么排呢?一共有三种.
orderby的值为1,执行order by price1 desc
orderby值为2时,执行order by price1 asc
orderby 值为空时,即用户不选择条件排序,那么order by id desc
nalnait 2008-10-09
  • 打赏
  • 举报
回复
CREATE procedure ProList
@myname varchar(100),
@orderby int
as
select id,myname,price from [pro]
where online=1
and myname like '%'+@myname+'%'

--这儿排序怎么写
order by case when @orderby is null then id else price1 end desc

GO
mugua604 2008-10-09
  • 打赏
  • 举报
回复

CREATE procedure ProList
@orderby int
as
begin
select top 10 id,type,subject from [table1] order by case @orderby when 1 then id when 2 then type end desc
end

luancode 2008-10-09
  • 打赏
  • 举报
回复
我不是根据不同的字段排序,是两种情况都有,当orderby不为空时,根据同一字段price升序或降序,当orderby为空时,根据另一字段id desc
Lori268 2008-10-09
  • 打赏
  • 举报
回复
自己动手改自己的吧
Lori268 2008-10-09
  • 打赏
  • 举报
回复

CREATE procedure ProList
@orderby int
as
begin
select top 10 id,type,subject from [table1] order by case @orderby when 1 then id when 2 then type end desc
end

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧