我在开发系统的时候,写了一个SQL代码,如下:
declare @CardID int,@pmember_login varchar(10),@pmember_pwd varchar(10)
declare @mycount int,@i int
select @i = 0
select @mycount = count(*) from [Job_p_pmember] where pmember_login ='7'
myLoop:
select @CardID=pmember_CardID from [Job_p_pmember] where pmember_login ='7'
if @CardID>0
begin
select @pmember_login= CardNo,@pmember_pwd = CardPwd from [Job_CardInfo] where CardID = @CardID
print @pmember_login 第一条结果:7079158
print @pmember_pwd 第一条结果:13038
--下面的语句将取到的值更新到pmember中
Update [pmember] Set pmember_login = @pmember_login,pmember_pwd =@pmember_pwd where pmember_CardID= @CardID(这里有问题)
select @i = @i+1
end
while (@i<@mycount)
goto myLoop更新后的数据库中的结果是pmember_login='7',为什么我已经将变量设置为字符型的了,更新的时候变成第一个数字了呢?
declare @CardID int,@pmember_login varchar(10),@pmember_pwd varchar(10)
declare @mycount int,@i int
select @i = 0
select @mycount = count(*) from [Job_p_pmember] where pmember_login ='7'
myLoop:
select @CardID=pmember_CardID from [Job_p_pmember] where pmember_login ='7'
if @CardID>0
begin
select @pmember_login= CardNo,@pmember_pwd = CardPwd from [Job_CardInfo] where CardID = @CardID
print @pmember_login 第一条结果:7079158
print @pmember_pwd 第一条结果:13038
--下面的语句将取到的值更新到pmember中
Update [pmember] Set pmember_login = @pmember_login,pmember_pwd =@pmember_pwd where pmember_CardID= @CardID(这里有问题)
select @i = @i+1
end
while (@i<@mycount)
goto myLoop更新后的数据库中的结果是pmember_login='7',为什么我已经将变量设置为字符型的了,更新的时候变成第一个数字了呢?
goto myLoop
select @CardID=pmember_CardID from [Job_p_pmember] where pmember_login ='7'
select @mycount = count(*) from [Job_p_pmember] where pmember_login ='''7'''
myLoop:
select @CardID=pmember_CardID from [Job_p_pmember] where pmember_login ='''7'''
if @CardID>0
print @pmember_login 第一条结果:7079158
print @pmember_pwd 第一条结果:13038
更新到数据库中时变成这样的:
7
1
只剩下了一个字符
from [pmember] aa inner join
(select a.cardNo,a.CardPwd,a.CardID from [Job_CardInfo] a inner join [Job_p_pmember] b on a.CardID=b.pmember_CardID where pmember_login ='7' and pmember_cardid>0) bb
on aa.pmember_CardID=bb.CardId
不过其中
select @CardID=pmember_CardID from [Job_p_pmember] where pmember_login ='7'
只能取得最后一条记录的pmember_cardid,也就是每次循环得到的@cardid的值是一样的
如要取得各条记录的pmember_cardID值,应该建一游标如下
declare job_p_pmember cursor for select pmember_cardid from job_p_pmember where pmember_login='7'
open job_p_pmember
fetch next from job_p_pmember into @cardid
while @@fetch_status=0
begin
对各个@cardid进行处理
........
fetch next from job_p_pmember into @cardid
end
select @pmember_login= CardNo,@pmember_pwd = CardPwd from [Job_CardInfo] where CardID = @CardID
print @pmember_login 第一条结果:7079158
print @pmember_pwd 第一条结果:13038
这是我取到的值,下一步我更新到数据库中:
Update [Job_p_pmember] Set pmember_login = @pmember_login,pmember_pwd =@pmember_pwd where pmember_CardID= @CardID(执行这条语句)然后我才读取pmember_login 和 pmember_pwd,结果是:
pmember_login:7
pmember_pwd:1
数据发生了截断现象,我想知道这是怎么回事?诶,回答问题的人太多了,准备加分到50
select @i = 0
select @mycount = count(*) from [Job_p_pmember] where pmember_login ='7'
select @i = @i+1
while (@i<@mycount)
这几句都没大用。光看以上的代码也看不清实质,你得给出表结构,数据,过程代码