查询结果处理？

[phoenixsoft]

select @chezhangID = Chezhang_ID
from Log_CTC
where Next_ID = NULL
if (@chezhangID = NULL)
begin
...
end
else
begin
...
end

Chezhang_ID和Next_ID字段定义如下：

Chezhang_ID varchar(8)
Next_ID varchar(8)

我想将满足条件的记录的Chezhang_ID字段放入变量@chezhangID中，然后根据它是否等于NULL来做其他处理，不知道这样写对吗？

[wzh1215]

select @chezhangID = Chezhang_ID
from Log_CTC
where Next_ID = NULL
if (@chezhangID is NULL)
begin
...
end
else
begin
...
end

[wzh1215]

错了一点点！
select @chezhangID = Chezhang_ID
from Log_CTC
where Next_ID is NULL
if (@chezhangID is NULL)
begin
...
end
else
begin
...
end

[welyngj]

不对，因为select Chezhang_ID
from Log_CTC
where Next_ID = NULL
是个集合，不能把集合的结果给单个变量

[welyngj]

应该用游标。

[welyngj]

declare @chengzhangid varchar(30)
declare cur cursor for select Chezhang_ID from Log_CTC
where Next_ID = NULL
open cur
fetch next from cur into @chengzhangid
while @@fetch_status=0
begin
if (@chezhangID = NULL)
begin
...
end
else
begin
...
end
end

[phoenixsoft]

to 平平淡淡：
我照你的方法写了个存储过程：

CREATE PROCEDURE CTC_Add
@preID varchar(8) = NULL,
@nextID varchar(8) = NULL,
@stationID varchar(8) = NULL,
@trainID varchar(8) = NULL,
@numID varchar(8) = NULL,
@groupID varchar(8) = NULL,
@driverID varchar(8) = NULL,
@count tinyint = 0
AS
declare @err int, @chezhangID varchar(8)
begin tran
declare CTC_cur cursor for select Chezhang_ID,Jiche_ID from Log_CTC where Next_ID is NULL
open CTC_cur
fetch next from CTC_cur into @chezhangID,@preID
while @@fetch_status = 0
begin
update Log_CTC
set Next_ID = @trainID
where Next_ID is NULL
end
set @err = @@error
if @err != 0
begin
rollback tran
return
end
if (@chezhangID is NULL)
begin
insert into
Log_CTC(Previor_ID,Next_ID,Chezhang_ID,Jiche_ID,Checi_ID,Chezhu_ID,
Driver_ID,Err_count)
values(@preID,@nextID,@stationID,@trainID,@numID,@groupID,@driverID,
@count)
end
else
begin
insert into
Log_CTC(Previor_ID,Next_ID,Chezhang_ID,Jiche_ID,Checi_ID,Chezhu_ID,
Driver_ID,Err_count)
values(@chezhangID,@nextID,@stationID,@trainID,@numID,@groupID,@driverID,
@count)
end
set @err = @@error
if @err != 0
begin
rollback tran
return
end
commit tran
GO


为什么加入的数据Previor_ID是NULL呢？

[phoenixsoft]

我是这样使用这个存储过程的：
EXEC CTC_Add @trainID = '1123'

[yujohny]

你这个存储过程到底要插入多少条记录？？？

[yujohny]

我觉得你这个判断语句是不是少了个条件，你说说你的存储过程意图
select @chezhangID = Chezhang_ID
from Log_CTC
where Next_ID = NULL

[phoenixsoft]

我的存储过程意图是：
将表中的Next_ID字段为空的记录a（只有一个）的Next_ID改为将要插入的记录b的Jiche_ID(即@trainID)，如果没有错的话，将记录b的Previor_ID的值改为记录a的Chezhang_ID或
Jiche_ID，最后将记录b插入。