2 etywu etywu 于 2016.03.03 17:40 提问

SQLSERVER存储过程,请各位帮忙看一下

第一条SQL语句执行没有数据则执行第二条SQL语句,第二条SQL语句没有数据则执行第三条语句

下面是SQL语句

第一句

  select * into #TemPoraryBgoods from (
 select distinct a.company,a.code,a.name,b.hyprice,a.comeFrom,finish=ISNULL(a.finish,''),c.smallfanwei,
 dbo.GetDistancefromLnglat(113.273364,23.218945,lng,lat)/1000 as distance from Bgoods a,Bgoodsdtl b,shopperA c 
 where a.company=c.SEQ and a.code=b.code and a.company=b.company and (c.lng between 113.224569198891 
 and 113.322158801109) and (c.lat between 23.174029235794 and 23.263860764206) and a.KgCN='茶' and 
 b.display in ('1','2','3')
 ) a where distance<=5 
 select * from (
 select ROW_NUMBER() over (order by distance) as 'rowNumber',* from #TemPoraryBgoods
 ) k where rowNumber between 1 and 20 
 select count(*) from #TemPoraryBgoods 

第二句

  select * into #TemPorarydhgoods from (
 select distinct a.company,a.code,a.oldcode,a.name,b.hyprice,a.comeFrom,finish=ISNULL(a.finish,''),
 dbo.GetDistancefromLnglat(113.273364,23.218945,lng,lat)/1000 as distance from prodh.dbo.G1982 a,prodh.dbo.G1982dtl b,
 shq520.dbo.ghuser c where a.company=c.SEQ and a.code=b.code and (c.lng between 113.224569198891 
 and 113.322158801109) and (c.lat between 23.174029235794 and 23.263860764206) and a.name like '%茶%' 
 and b.dhdisplay='1'
 ) a where distance<=5 
 select * from (
 select ROW_NUMBER() over (order by distance) as 'rowNumber',* from #TemPorarydhgoods
 ) k where rowNumber between 1 and 20 
 select count(*) from #TemPorarydhgoods 

第三句

  select distinct top 20 a.company,a.code,a.name,b.hyprice,a.comeFrom,finish=ISNULL(a.finish,''),c.smallfanwei,
 dbo.GetDistancefromLnglat(113.273364,23.218945,lng,lat)/1000 as distance from Bgoods a,Bgoodsdtl b,shopperA c 
 where a.company=c.SEQ and a.code=b.code and a.company=b.company and b.display in ('1','2','3') 
 and (c.lng between 113.224569198891 and 113.322158801109) and (c.lat between 23.174029235794 
 and 23.263860764206) order by distance

3个回答

SPE_Andy
SPE_Andy   2016.03.03 18:13
已采纳

第一条SQL语句执行没有数据则执行第二条SQL语句,第二条SQL语句没有数据则执行第三条语句 这是你要实现的功能吧。
我大致说下吧,

BEGING
第一句
exception when nodatefound then
begin
第二句
exception when nodatefound then
第三句
end;
end;

大概结构就是这样。你去具体的写完试试。当第一句没数据时,会报异常,将其捕获,并进行处理,也就是执行第二句。如果还是没数据,继续报异常,继续处理。这是我的方法。请参考。

etywu
etywu 谢谢你,我已经改好了
接近 2 年之前 回复
etywu
etywu 回复SPE_Andy: 我的数据库是SQLSERVER2008_R2
接近 2 年之前 回复
SPE_Andy
SPE_Andy 回复光明天使米迦勒: 代码都给你写了,还怎么具体啊
接近 2 年之前 回复
etywu
etywu 具体怎么做
接近 2 年之前 回复
enpterexpress
enpterexpress   2016.03.03 17:53

希望你能把你的需求说出来

etywu
etywu SQL语句我贴上去了
接近 2 年之前 回复
SPE_Andy
SPE_Andy   2016.03.04 09:35

我上面的就是代码啊。

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!