动态SQL exec如何写
0
悬赏园豆：20
[已解决问题]
解决于 2011-01-02 22:40
下面是我写的一个存储过程，一个别名作为了变量，需要用到动态SQL，但我怎么写都不对，无法保存，请赐教。
CREATE PROCEDURE chaxun
@a char
AS
exec(''
select m.daima as '代码',m.mingcheng as '名称',m.xianjia as '现价',m.zhangfu as '涨幅',
cast((((m.xianjia)/(x.zg)-1)*100) as decimal (18,2)) as '5日跌幅',
cast((((m.xianjia)/(s.zg)-1)*100) as decimal (18,2)) as '25日跌幅',
cast((((m.xianjia)/(x.zd)-1)*100) as decimal (18,2)) as '5日涨幅',
cast((((m.xianjia)/(s.zd)-1)*100) as decimal (18,2)) as '25日涨幅',
cast(((x.zg-m.xianjia)/(x.zg-x.zd)*100) as decimal (18,2)) as '5日威廉指标',
cast(((s.zg-m.xianjia)/(s.zg-s.zd)*100) as decimal (18,2)) as '25日威廉指标',
cast((x.zf) as decimal (18,2)) as '5日振幅',
cast((s.zf) as decimal (18,2)) as '25日振幅',
cast((((m.xianjia)/(x.ax)-1)*100) as decimal (18,2)) as '5日均线',
cast((((m.xianjia)/(s.ax)-1)*100) as decimal (18,2)) as '25日均线',
cast((x.zz) as decimal (18,2)) as '5日量能',
cast((s.zz) as decimal (18,2)) as '25日量能',
cast(((x.qj-1)*100) as decimal (18,2)) as '震荡区间',
cast((cast(m.zongliang as decimal)/cast(n.zongliang as decimal)-1) as decimal (18,2)) as '当日增量' from
(select b.* from
(select a.* ,
px = (select count(1) from meirishuju where daima = a.daima and date > a.date) + 1
from meirishuju a ) b
where b.px=1) m,
(select b.* from
(select a.* ,
px = (select count(1) from meirishuju where daima = a.daima and date > a.date) + 1
from meirishuju a ) b
where b.px=2) n,
(select b.daima,max(zuigao) zg,min(zuidi) zd,avg(zhengfu) zf,max(zuigao)/min(zuidi) qj,avg(xianjia) ax,
sum(zhangfu*zongjine)/avg(zongjine) zz from
(select a.* ,
px = (select count(1) from meirishuju where daima = a.daima and date > a.date) + 1
from meirishuju a ) b
where b.px between 1 and 5
group by b.daima ) x,
(select b.daima,max(zuigao) zg,min(zuidi) zd,avg(zhengfu) zf,max(zuigao)/min(zuidi) qj,avg(xianjia) ax,
sum(zhangfu*zongjine)/avg(zongjine) zz from
(select a.* ,
px = (select count(1) from meirishuju where daima = a.daima and date > a.date) + 1
from meirishuju a ) b
where b.px between 1 and 25
group by b.daima ) s
where m.daima=n.daima and m.daima=x.daima and m.daima=s.daima
and m.daima in (select daima from daima where biaoji in(2))
and n.zongliang <>0
order by '+@a+' desc
'')
GO
问题补充：
可以保存了，语法没错，但execute的时候，出来的结果还是不会排序，不知道为什么。
例如 execute chaxun '涨幅'
最佳答案
0
不能排序是因为你在定义参数的时候没给char范围
把char改为char(30)
CREATE PROCEDURE chaxun
@a char(30)
AS
exec('
select m.daima as ''代码'',m.mingcheng as ''名称'',m.xianjia as ''现价'',m.zhangfu as ''涨幅'',
cast((((m.xianjia)/(x.zg)-1)*100) as decimal (18,2)) as ''5日跌幅'',
cast((((m.xianjia)/(s.zg)-1)*100) as decimal (18,2)) as ''25日跌幅'',
cast((((m.xianjia)/(x.zd)-1)*100) as decimal (18,2)) as ''5日涨幅'',
cast((((m.xianjia)/(s.zd)-1)*100) as decimal (18,2)) as ''25日涨幅'',
cast(((x.zg-m.xianjia)/(x.zg-x.zd)*100) as decimal (18,2)) as ''5日威廉指标'',
cast(((s.zg-m.xianjia)/(s.zg-s.zd)*100) as decimal (18,2)) as ''25日威廉指标'',
cast((x.zf) as decimal (18,2)) as ''5日振幅'',
cast((s.zf) as decimal (18,2)) as ''25日振幅'',
cast((((m.xianjia)/(x.ax)-1)*100) as decimal (18,2)) as ''5日均线'',
cast((((m.xianjia)/(s.ax)-1)*100) as decimal (18,2)) as ''25日均线'',
cast((x.zz) as decimal (18,2)) as ''5日量能'',
cast((s.zz) as decimal (18,2)) as ''25日量能'',
cast(((x.qj-1)*100) as decimal (18,2)) as ''震荡区间'',
cast((cast(m.zongliang as decimal)/cast(n.zongliang as decimal)-1) as decimal (18,2)) as ''当日增量'' from
(select b.* from
(select a.* ,
px = (select count(1) from meirishuju where daima = a.daima and date > a.date) + 1
from meirishuju a ) b
where b.px=1) m,
(select b.* from
(select a.* ,
px = (select count(1) from meirishuju where daima = a.daima and date > a.date) + 1
from meirishuju a ) b
where b.px=2) n,
(select b.daima,max(zuigao) zg,min(zuidi) zd,avg(zhengfu) zf,max(zuigao)/min(zuidi) qj,avg(xianjia) ax,
sum(zhangfu*zongjine)/avg(zongjine) zz from
(select a.* ,
px = (select count(1) from meirishuju where daima = a.daima and date > a.date) + 1
from meirishuju a ) b
where b.px between 1 and 5
group by b.daima ) x,
(select b.daima,max(zuigao) zg,min(zuidi) zd,avg(zhengfu) zf,max(zuigao)/min(zuidi) qj,avg(xianjia) ax,
sum(zhangfu*zongjine)/avg(zongjine) zz from
(select a.* ,
px = (select count(1) from meirishuju where daima = a.daima and date > a.date) + 1
from meirishuju a ) b
where b.px between 1 and 25
group by b.daima ) s
where m.daima=n.daima and m.daima=x.daima and m.daima=s.daima
and m.daima in (select daima from daima where biaoji in(2))
and n.zongliang <>0
order by '+@a+' desc
')
GO
收获园豆：10
其他回答(1)
0
CREATE PROCEDURE chaxun
@a char
AS
exec(
'select m.daima as ''代码'',m.mingcheng as ''名称'',m.xianjia as ''现价'',m.zhangfu as ''涨幅'',
cast((((m.xianjia)/(x.zg)-1)*100) as decimal (18,2)) as ''5日跌幅'',
cast((((m.xianjia)/(s.zg)-1)*100) as decimal (18,2)) as ''25日跌幅'',
cast((((m.xianjia)/(x.zd)-1)*100) as decimal (18,2)) as ''5日涨幅'',
cast((((m.xianjia)/(s.zd)-1)*100) as decimal (18,2)) as ''25日涨幅'',
cast(((x.zg-m.xianjia)/(x.zg-x.zd)*100) as decimal (18,2)) as ''5日威廉指标'',
cast(((s.zg-m.xianjia)/(s.zg-s.zd)*100) as decimal (18,2)) as ''25日威廉指标'',
cast((x.zf) as decimal (18,2)) as ''5日振幅'',
cast((s.zf) as decimal (18,2)) as ''25日振幅'',
cast((((m.xianjia)/(x.ax)-1)*100) as decimal (18,2)) as ''5日均线'',
cast((((m.xianjia)/(s.ax)-1)*100) as decimal (18,2)) as ''25日均线'',
cast((x.zz) as decimal (18,2)) as ''5日量能'',
cast((s.zz) as decimal (18,2)) as ''25日量能'',
cast(((x.qj-1)*100) as decimal (18,2)) as ''震荡区间'',
cast((cast(m.zongliang as decimal)/cast(n.zongliang as decimal)-1) as decimal (18,2)) as ''当日增量'' from
(select b.* from
(select a.* ,
px = (select count(1) from meirishuju where daima = a.daima and date > a.date) + 1
from meirishuju a ) b
where b.px=1) m,
(select b.* from
(select a.* ,
px = (select count(1) from meirishuju where daima = a.daima and date > a.date) + 1
from meirishuju a ) b
where b.px=2) n,
(select b.daima,max(zuigao) zg,min(zuidi) zd,avg(zhengfu) zf,max(zuigao)/min(zuidi) qj,avg(xianjia) ax,
sum(zhangfu*zongjine)/avg(zongjine) zz from
(select a.* ,
px = (select count(1) from meirishuju where daima = a.daima and date > a.date) + 1
from meirishuju a ) b
where b.px between 1 and 5
group by b.daima ) x,
(select b.daima,max(zuigao) zg,min(zuidi) zd,avg(zhengfu) zf,max(zuigao)/min(zuidi) qj,avg(xianjia) ax,
sum(zhangfu*zongjine)/avg(zongjine) zz from
(select a.* ,
px = (select count(1) from meirishuju where daima = a.daima and date > a.date) + 1
from meirishuju a ) b
where b.px between 1 and 25
group by b.daima ) s
where m.daima=n.daima and m.daima=x.daima and m.daima=s.daima
and m.daima in (select daima from daima where biaoji in(2))
and n.zongliang <>0
order by '+@a+' desc
')
GO
收获园豆：10
