sos请教大虾

[cqjwin]

怎么把要进行排序的字段和方式（desc/asc）通过存储过程的参数进行带入到存储过程中，

[gohst001]

动态方式执行你要跑的sql语句。

[LouisXIV]

--诚意，lz的SOS的诚意在哪里？

--^^

if exists(select 1 from sysobjects where id=object_id('testtable') and xtype='U') drop table testtable
create table testtable
(
id int
)

insert into testtable
select 1 union all
select 3 union all
select 5 union all
select 2 union all
select 4

declare @b varchar(5)
set @b='desc'
exec ('select * from testtable order by id '+@b)

set @b='asc'
exec ('select * from testtable order by id '+@b)
/*
id
-----------
5
4
3
2
1

id
-----------
1
2
3
4
5

*/

[cqjwin]

请教楼上，
insert into testtable
select 1 union all
select 3 union all
select 5 union all
select 2 union all
select 4
有什么用途！

[fcuandy]

将1,2,3,4,5当作五条记录分别插入表testtable
用all是指当值有重复时继续插入.如:

select 1 union all
select 3 union all
select 5 union all
select 2 union all
select 4 union all
select 4

会插入6条记录,两个4
如果没有 all

select 1 union
select 3 union
select 5 union
select 2 union
select 4 union
select 4
会插入5条记录.

这是基本的union (All)语法.

至于总的语句 INSERT SELECT 也是基本的语法,将 SELECT 出的行插入到指定表(已存在的表).

[LouisXIV]

顺便说一下，只用Union时，SQL会自说自话地帮你排序

[cqjwin]

还是不行呀，我的存储过和如下，请帮我解决一下！
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER proc selchuanwu
as
declare @bc varchar(4)
set @bc='desc'
select did as '订单号',kid as '款号',poid as 'PO号',customer as '客户编号',
ddang as '担当',lbie as '类别',leibiedm.cpm as '品名',ddshu as '订单数',djia as '单价',
jine as '金额',mllu as '毛利率',yjmli as '预计毛利',qyueqi as '签约日期',
htqi as '合同日期',fkfshi as '付款方式',cyfshi as '出运方式',address as '目的地'
from chuanwu,leibiedm
where
chuanwu.lbie=leibiedm.dm
order by did @bc

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[cqjwin]

但是如果我要这样
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER proc selchuanwu
as
declare @ps varchar(4)
declare @lm varchar(10)
set @bc='desc'
select did as '订单号',kid as '款号',poid as 'PO号',customer as '客户编号',
ddang as '担当',lbie as '类别',leibiedm.cpm as '品名',ddshu as '订单数',djia as '单价',
jine as '金额',mllu as '毛利率',yjmli as '预计毛利',qyueqi as '签约日期',
htqi as '合同日期',fkfshi as '付款方式',cyfshi as '出运方式',address as '目的地'
from chuanwu,leibiedm
where
chuanwu.lbie=leibiedm.dm
order by @lm+@bc

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


如果是这样的话，就会出现错误

[cqjwin]

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER proc selchuanwu（@lm varchar(20),@ps varchar(4)）
as

set @bc='desc'
select did as '订单号',kid as '款号',poid as 'PO号',customer as '客户编号',
ddang as '担当',lbie as '类别',leibiedm.cpm as '品名',ddshu as '订单数',djia as '单价',
jine as '金额',mllu as '毛利率',yjmli as '预计毛利',qyueqi as '签约日期',
htqi as '合同日期',fkfshi as '付款方式',cyfshi as '出运方式',address as '目的地'
from chuanwu,leibiedm
where
chuanwu.lbie=leibiedm.dm
order by @lm+@bc

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


如果是这样的话，就会出现错误

[LouisXIV]

lz根本没有仔细看

是要用动态语句来拼接字符串后再执行

[cqjwin]

兄弟什么叫“动态语句拼接字符串”，我是真的不懂我的错在那里，我要用这个过程的时候，我就可以在用的时候输入相应的参数呀！

[liangpei2008]

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc selchuanwu(@lm varchar(20),@ps varchar(4))
as
declare @sql varchar(500)
declare @bc varchar(10)
set @bc='desc'
select @sql='select did as ''订单号'',kid as ''款号'',poid as ''PO号'',customer as ''客户编号'',
ddang as ''担当'',lbie as ''类别'',leibiedm.cpm as ''品名'',ddshu as ''订单数'',djia as ''单价'',
jine as ''金额'',mllu as ''毛利率'',yjmli as ''预计毛利'',qyueqi as ''签约日期'',
htqi as ''合同日期'',fkfshi as ''付款方式'',cyfshi as ''出运方式'',address as ''目的地''
from chuanwu,leibiedm
where
chuanwu.lbie=leibiedm.dm
order by '+ @lm+''+@bc
exec @sql
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON