problem in stored proc @@rowcount not working

• • Reply

  13 years ago

  by avinash5

  avi pawar

  Pune, India

  Joined 14 years ago

  -- in middle of this proc where i m getting stuck i mention some comment plz have a look on it near if @@rowcount loop

   

  Declare @FileId numeric
  Declare @CustNAme nvarchar(200)
  Declare @CustAdd nvarchar(200)
  Declare @ResPhone nvarchar(200)
  Declare @Mobile nvarchar(200)
  Declare @OffNAme nvarchar(200)
  Declare @OffAdd nvarchar(200)
  Declare @OffPhone nvarchar(100)
  Declare @SrNo int
  DECLARE @spot SMALLINT, @str VARCHAR(200), @Fstr VARCHAR(200),@Sstr VARCHAR(200),@Tstr VARCHAR(200)
  DECLARE @spaceCnt SMALLINT
  DECLARE @Lspot SMALLINT, @CmprFstr VARCHAR(200),@CmprSstr VARCHAR(200)
  set @FileID=6
  DECLARE GetRecord CURSOR FOR
   select CustomerName,Address,resPhone,Mobile,OfficeName,OffAddress,OffPhone,SrNO from DDupDlyXL where FileID=@FileID
  open  GetRecord
   fetch next from GetRecord into @CustNAme,@CustAdd,@ResPhone,@Mobile,@OffNAme,@OffAdd,@OffPhone,@SrNo
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  WHILE @@Fetch_Status=0
      BEGIN
  -- this will count spaces
   
   set @CustNAme=REPLACE(@CustNAme, space(2), ' ')
   SELECT @SpaceCnt=LEN(@CustNAme) - LEN(REPLACE(@CustNAme, ' ', ''))           
         
    SET @spot = CHARINDEX(' ', @CustNAme)
    IF @spot>0 
              BEGIN 
                  SET @str = LEFT(@CustNAme, @spot-1)
    set @FStr=@str+'%'
     END 
          ELSE 
              BEGIN 
                  SET @str = @CustNAme
                  SET @CustNAme = '' 
              END 
   SET @Lspot =CHARINDEX(' ', REVERSE(@CustNAme))
   IF @Lspot>0 
              BEGIN 
                  SET @str = LEFT(REVERSE(@CustNAme), @Lspot-1)
    set @SStr=reverse(@str)+'%'
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

                
              END 
          ELSE 
              BEGIN 
                  SET @str = @CustNAme
                  SET @CustNAme = '' 
              END 
   set @cmprFStr='%'+@FSTR+@SSTR
   set @cmprSStr='%'+@SSTR+@FSTR
  
  
  
  
  
  
  
  

  --insert into TempSearchData(CustomerNAme,Address,ResPhone,Mobile,OfficeName,OffAddress,OffPhone,FoundName,Srno,BankName,Remark,XlSrNO,FileID)
  select @CustNAme,@CustAdd,@ResPhone,@Mobile,@OffNAme,@OffAdd,@OffPhone,CustomerNAme,SrNo,BankName,'.',@srno,@FileId from DeDupeData where CustomerName like @CustName or CustomerName like @cmprFStr or CustomerName like @cmprSStr
  if @@Rowcount>0  --here if i m checking the recordcount, if recordcount >0 then inserting data in tempsearch table by selecting data from DDdata table but though here i get recordcount=0 its not going in else loop . i dont know why its not working plz give m suggesion if anything  wrong here
  begin
   insert into TempSearchData(CustomerNAme,Address,ResPhone,Mobile,OfficeName,OffAddress,OffPhone,FoundName,Srno,BankName,Remark,XlSrNO,FileID)
   select @CustNAme,@CustAdd,@ResPhone,@Mobile,@OffNAme,@OffAdd,@OffPhone,CustomerNAme,SrNo,BankName,'.',@srno,@FileId from DeDupeData where CustomerName like @CustName or CustomerName like @cmprFStr or CustomerName like @cmprSStr
  end
  else
  begin
   insert into TempSearchData(CustomerNAme,Address,ResPhone,Mobile,OfficeName,OffAddress,OffPhone,FoundName,Srno,BankName,Remark,XlSrNO,FileID)
   values(@CustNAme,@CustAdd,@ResPhone,@Mobile,@OffNAme,@OffAdd,@OffPhone,'.','.','.','OK',@srno,@FileId)
  
  
  
  
  
  
  
  
  
  

  end

   fetch next from GetRecord into @CustNAme,@CustAdd,@ResPhone,@Mobile,@OffNAme,@OffAdd,@OffPhone,@SrNo

    END

   Close GetRecord
   Deallocate GetRecord
  select CustomerNAme,Address,ResPhone,Mobile,OfficeName,OffAddress,OffPhone,xlSrNo,FoundName,Remark,Srno,BankName,FileID from TempSearchData order by xlsrno
  GO
  
  
  
  
  

  Report abuse