-- 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
No one has replied yet! Why not be the first?
Sign in or Join us (it's free).