如果可以的话将pro_smp_Output也贴出来看看再看看 Select StationName From TrainTime Where TrainID = @cTrainID Order By StationID Asc 查出来的结果
ALTER Procedure pro_smp_live_train_2( @cOperationCode Char(21), --内部协议的业务代码 @cSrcAdd Char(21), --发送命令者的手机号 @cTrainID Char(6)) --列车的车次号码 As Begin --查询列车经过的所有站台名 --根据用户要求查询的列车类型进行查找 --并将查找到结果整理成完整的语言来描述出来 Declare @cMsg Varchar(400) Declare @cStationName VarChar(16) Declare @bHaveRecord Tinyint --用于判断是否找到纪录的标志位 Set @bHaveRecord = 0 Set @cMsg = rtrim(@cTrainID) + '经过的车站依次是:'
Begin Declare train_cursor Cursor For Select StationName From TrainTime Where TrainID = @cTrainID Order By StationID Asc
Open train_cursor
Fetch Next From train_cursor Into @cStationName --SELECT @cStationName While @@Fetch_Status = 0 Begin Set @bHaveRecord = 1 Set @cMsg = @cMsg + rtrim(@cStationName) + ','
SELECT @cMsg --Test @cMsg to find it is true?? Fetch Next From train_cursor INTO @cStationName
End Close train_cursor Deallocate train_cursor End
--如果查不到车次,就返回提示信息 If @bHaveRecord = 0 Begin --设置错误提示信息 Declare @cErrTipTrainID Varchar(50) Set @cErrTipTrainID = '查找的车次并不存在,可发CB获得帮助' Exec pro_smp_Output @cSrcAdd, @cOperationCode, @cErrTipTrainID, @cOperationCode, 0 Return End --按照命令字查找收费配置,并将短信内容发给调用者 Exec pro_smp_Output @cSrcAdd, @cOperationCode, @cMsg, @cOperationCode, 1 End
这样改吧:ALTER Procedure pro_smp_live_train_2( @cOperationCode Char(21), --内部协议的业务代码 @cSrcAdd Char(21), --发送命令者的手机号 @cTrainID Char(6)) --列车的车次号码 As Begin --查询列车经过的所有站台名 --根据用户要求查询的列车类型进行查找 --并将查找到结果整理成完整的语言来描述出来 Declare @cMsg Varchar(400) Declare @cStationName VarChar(16) Declare @bHaveRecord Tinyint --用于判断是否找到纪录的标志位 Set @bHaveRecord = 0 Set @cMsg = rtrim(@cTrainID) + '经过的车站依次是:' if not exists (Select StationName From TrainTime Where TrainID = @cTrainID) Begin --如果查不到车次,就返回提示信息 --设置错误提示信息 Declare @cErrTipTrainID Varchar(50) Set @cErrTipTrainID = '查找的车次并不存在,可发CB获得帮助' Exec pro_smp_Output @cSrcAdd, @cOperationCode, @cErrTipTrainID, @cOperationCode, 0 Return End else Begin Select @cMsg=@cMsg+rtrim(StationName)+ ',' From TrainTime Where TrainID = @cTrainID Order By StationID Asc --按照命令字查找收费配置,并将短信内容发给调用者 Exec pro_smp_Output @cSrcAdd, @cOperationCode, @cMsg, @cOperationCode, 1
End End
不用游标ALTER Procedure pro_smp_live_train_2( @cOperationCode Char(21), --内部协议的业务代码 @cSrcAdd Char(21), --发送命令者的手机号 @cTrainID Char(6)) --列车的车次号码 As Begin --查询列车经过的所有站台名 --根据用户要求查询的列车类型进行查找 --并将查找到结果整理成完整的语言来描述出来 Declare @cMsg Varchar(400) Set @cMsg = rtrim(@cTrainID) + '经过的车站依次是:' if not exists (Select StationName From TrainTime Where TrainID = @cTrainID) Begin --如果查不到车次,就返回提示信息 --设置错误提示信息 Declare @cErrTipTrainID Varchar(50) Set @cErrTipTrainID = '查找的车次并不存在,可发CB获得帮助' Exec pro_smp_Output @cSrcAdd, @cOperationCode, @cErrTipTrainID, @cOperationCode, 0 Return End else Begin Select @cMsg=@cMsg+rtrim(StationName)+ ',' From TrainTime Where TrainID = @cTrainID Order By StationID Asc --按照命令字查找收费配置,并将短信内容发给调用者 Exec pro_smp_Output @cSrcAdd, @cOperationCode, @cMsg, @cOperationCode, 1
Where TrainID = @cTrainID
Order By StationID Asc
查出来的结果
@cOperationCode Char(21), --内部协议的业务代码
@cSrcAdd Char(21), --发送命令者的手机号
@cTrainID Char(6)) --列车的车次号码
As
Begin --查询列车经过的所有站台名
--根据用户要求查询的列车类型进行查找
--并将查找到结果整理成完整的语言来描述出来
Declare @cMsg Varchar(400)
Declare @cStationName VarChar(16)
Declare @bHaveRecord Tinyint --用于判断是否找到纪录的标志位
Set @bHaveRecord = 0
Set @cMsg = rtrim(@cTrainID) + '经过的车站依次是:'
Begin
Declare train_cursor Cursor For
Select StationName From TrainTime
Where TrainID = @cTrainID
Order By StationID Asc
Open train_cursor
Fetch Next From train_cursor
Into @cStationName
--SELECT @cStationName
While @@Fetch_Status = 0
Begin
Set @bHaveRecord = 1
Set @cMsg = @cMsg + rtrim(@cStationName) + ','
SELECT @cMsg --Test @cMsg to find it is true?? Fetch Next From train_cursor INTO @cStationName
End
Close train_cursor
Deallocate train_cursor
End
--如果查不到车次,就返回提示信息
If @bHaveRecord = 0
Begin
--设置错误提示信息
Declare @cErrTipTrainID Varchar(50)
Set @cErrTipTrainID = '查找的车次并不存在,可发CB获得帮助'
Exec pro_smp_Output @cSrcAdd, @cOperationCode, @cErrTipTrainID, @cOperationCode, 0
Return
End --按照命令字查找收费配置,并将短信内容发给调用者
Exec pro_smp_Output @cSrcAdd, @cOperationCode, @cMsg, @cOperationCode, 1
End
@cOperationCode Char(21), --内部协议的业务代码
@cSrcAdd Char(21), --发送命令者的手机号
@cTrainID Char(6)) --列车的车次号码
As
Begin --查询列车经过的所有站台名
--根据用户要求查询的列车类型进行查找
--并将查找到结果整理成完整的语言来描述出来
Declare @cMsg Varchar(400)
Declare @cStationName VarChar(16)
Declare @bHaveRecord Tinyint --用于判断是否找到纪录的标志位
Set @bHaveRecord = 0
Set @cMsg = rtrim(@cTrainID) + '经过的车站依次是:'
if not exists (Select StationName From TrainTime
Where TrainID = @cTrainID)
Begin
--如果查不到车次,就返回提示信息
--设置错误提示信息
Declare @cErrTipTrainID Varchar(50)
Set @cErrTipTrainID = '查找的车次并不存在,可发CB获得帮助'
Exec pro_smp_Output @cSrcAdd, @cOperationCode, @cErrTipTrainID, @cOperationCode, 0
Return
End
else
Begin
Select @cMsg=@cMsg+rtrim(StationName)+ ',' From TrainTime
Where TrainID = @cTrainID
Order By StationID Asc
--按照命令字查找收费配置,并将短信内容发给调用者
Exec pro_smp_Output @cSrcAdd, @cOperationCode, @cMsg, @cOperationCode, 1
End
End
@cOperationCode Char(21), --内部协议的业务代码
@cSrcAdd Char(21), --发送命令者的手机号
@cTrainID Char(6)) --列车的车次号码
As
Begin --查询列车经过的所有站台名
--根据用户要求查询的列车类型进行查找
--并将查找到结果整理成完整的语言来描述出来
Declare @cMsg Varchar(400) Set @cMsg = rtrim(@cTrainID) + '经过的车站依次是:'
if not exists (Select StationName From TrainTime
Where TrainID = @cTrainID)
Begin
--如果查不到车次,就返回提示信息
--设置错误提示信息
Declare @cErrTipTrainID Varchar(50)
Set @cErrTipTrainID = '查找的车次并不存在,可发CB获得帮助'
Exec pro_smp_Output @cSrcAdd, @cOperationCode, @cErrTipTrainID, @cOperationCode, 0
Return
End
else
Begin
Select @cMsg=@cMsg+rtrim(StationName)+ ',' From TrainTime
Where TrainID = @cTrainID
Order By StationID Asc
--按照命令字查找收费配置,并将短信内容发给调用者
Exec pro_smp_Output @cSrcAdd, @cOperationCode, @cMsg, @cOperationCode, 1
End
End
我加上了distinct,这时orderby子句报错,我去掉distinct,再去掉orderby,这时返回了正确的结果,为什么,orderby该怎么用