首页 新闻 赞助 找找看

动态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 '涨幅'
xzf_fancy的主页 xzf_fancy | 初学一级 | 园豆:28
提问于:2010-12-11 22:30
< >
分享
最佳答案
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
追索 | 小虾三级 |园豆:625 | 2010-12-14 09:36
其他回答(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
I,Robot | 园豆:9783 (大侠五级) | 2010-12-12 09:41
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册