regarding a peculiar problem in sql server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

regarding a peculiar problem in sql server

Author

Topic

marijo2k
Starting Member

2 Posts

Posted - 2008-08-14 : 05:51:04

Hi ,

Written a stored procedure to migrate data from one table in to 4 different tables.
Selecting the data from the source table and storing it in a cursor before inserting in to the respective tables.

In the select statement if i use 'top 10' records, the insert happens fine, however when i just select the record, only one record is getting inserted.

Wehn i execute the select statement individually, it returns 6 records that needs to be inerted irrespective of using 'top' key word in the select statement or not.But while executing the stored procedure without the top key word in the select statement only one of the six record gets inserted.Plz help..

select statement :
select b.CUST_NO_N,
CUST_NAME_V,
CUST_ADDR1_V,
CUST_ADDR2_V,
CUST_ADDR3_V,
CUST_RESTELNO_N,
CUST_OFFTELNO_N,
CUST_EMAILADDR_V,
CUST_SETUPDT_D,
CUST_MOBTELNO_N,
FUNC_ID_N,
ARNG_NO_V,
ARNG_TYPE_C,
REGN_DT_D,
NOTIFY_TYPE_C,
NOTIFY_ON_C
from v_customer_nbd a,Subscriptions_NBD b
where a.CUST_NO_N = b.CUST_NO_N
and b.status_c = 'A'
and a.migrt_status is null
and a.CUST_STATUS_C='A'
and b.func_id_n <> 6
and a.cust_no_n='191892'
order by CUST_NO_N

with keyword top:

select top 10 b.CUST_NO_N,
CUST_NAME_V,
CUST_ADDR1_V,
CUST_ADDR2_V,
CUST_ADDR3_V,
CUST_RESTELNO_N,
CUST_OFFTELNO_N,
CUST_EMAILADDR_V,
CUST_SETUPDT_D,
CUST_MOBTELNO_N,
FUNC_ID_N,
ARNG_NO_V,
ARNG_TYPE_C,
REGN_DT_D,
NOTIFY_TYPE_C,
NOTIFY_ON_C
from v_customer_nbd a,Subscriptions_NBD b
where a.CUST_NO_N = b.CUST_NO_N
and b.status_c = 'A'
and a.migrt_status is null
and a.CUST_STATUS_C='A'
and b.func_id_n <> 6
and a.cust_no_n='191892'
order by CUST_NO_N

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 06:23:16

are you sure you need to use cursor for this? if yes, whats the main processing you're doing with cursor?

thiyait
Yak Posting Veteran

70 Posts

Posted - 2008-08-14 : 06:30:29

can you post ur cursor code.

Thiya
http://knowmoreax.blogspot.com/

marijo2k
Starting Member

2 Posts

Posted - 2008-08-14 : 08:23:56

quote:
Originally posted by thiyait

can you post ur cursor code.

Thiya
http://knowmoreax.blogspot.com/

This is my complete procedure for migration of data

ALTER procedure sp_nbd_data_migration
as
declare @CUST_NO_N varchar(10),
@CUST_NAME_V varchar(80),
@CUST_ADDR1_V varchar(40),
@CUST_ADDR2_V varchar(40),
@CUST_ADDR3_V varchar(40),
@CUST_RESTELNO_N varchar(12),
@CUST_OFFTELNO_N varchar(12),
@CUST_EMAILADDR_V varchar(40),
@CUST_SETUPDT_D datetime,
@CUST_MOBTELNO_N varchar(16),
@FUNC_ID_N int,
@ARNG_NO_V varchar(25),
@ARNG_TYPE_C char(1),
@REGN_DT_D datetime,
@NOTIFY_TYPE_C char(1),
@NOTIFY_ON_C char(3)

declare @ErrMsg NVARCHAR(MAX),
@servCode varchar(5),
@psabweekly varchar(2),
@psabtime varchar(2),
@psabmonthly varchar(2)

declare @datacol cursor

set @datacol = cursor for
select b.CUST_NO_N,
CUST_NAME_V,
CUST_ADDR1_V,
CUST_ADDR2_V,
CUST_ADDR3_V,
CUST_RESTELNO_N,
CUST_OFFTELNO_N,
CUST_EMAILADDR_V,
CUST_SETUPDT_D,
CUST_MOBTELNO_N,
FUNC_ID_N,
ARNG_NO_V,
ARNG_TYPE_C,
REGN_DT_D,
NOTIFY_TYPE_C,
NOTIFY_ON_C
from v_customer_nbd a,Subscriptions_NBD b
where a.CUST_NO_N = b.CUST_NO_N
and b.status_c = 'A'
and a.migrt_status is null
and a.CUST_STATUS_C='A'
and b.func_id_n <> 6
--and a.cust_no_n='191892'
order by CUST_NO_N

open @datacol

fetch next from @datacol into @CUST_NO_N, @CUST_NAME_V, @CUST_ADDR1_V,
@CUST_ADDR2_V, @CUST_ADDR3_V, @CUST_RESTELNO_N, @CUST_OFFTELNO_N, @CUST_EMAILADDR_V,
@CUST_SETUPDT_D, @CUST_MOBTELNO_N, @FUNC_ID_N, @ARNG_NO_V, @ARNG_TYPE_C, @REGN_DT_D, @NOTIFY_TYPE_C, @NOTIFY_ON_C

while @@fetch_status = 0
BEGIN
IF len(@CUST_MOBTELNO_N) > 6
set @CUST_MOBTELNO_N='0097150'+substring(@CUST_MOBTELNO_N,len(@CUST_MOBTELNO_N)-6,len(@CUST_MOBTELNO_N))
BEGIN TRANSACTION
BEGIN TRY

IF NOT EXISTS(SELECT CMCUSTID FROM CUSTMASTER WHERE CMCUSTID=@CUST_NO_N )
BEGIN
--write CustMaster record with status as 1 since BankNET maintains active status
insert into custmaster values(@CUST_NO_N,@CUST_NAME_V,@CUST_ADDR1_V,@CUST_ADDR2_V,@CUST_ADDR3_V,'',@CUST_RESTELNO_N,@CUST_OFFTELNO_N,@CUST_EMAILADDR_V,'12','0003946537822','1','MIGRATED',getdate(),'NBD')
Print 'am in custmaster'
-- IF @@ERROR <> 0
-- GOTO ERROR_HANDLERR;
END

IF NOT EXISTS(SELECT CACUSTID FROM custAccount WHERE caCustID=@CUST_NO_N AND caAccountNo=@ARNG_NO_V)
BEGIN
insert into custAccount(caCustID,caAccountNo,caStatus,caReqType,caUpdateD,caisdefault,cabank) values (@CUST_NO_N,@ARNG_NO_V,@ARNG_TYPE_C,'PS',getdate(),'1','NBD')
Print 'am in custaccount'
-- IF @@ERROR <> 0
-- GOTO ERROR_HANDLERR;
END

IF NOT EXISTS(SELECT cgCustID FROM CUSTGSM WHERE CGCUSTID=@CUST_NO_N AND cgGSMNo=@CUST_MOBTELNO_N)
BEGIN
insert into custGSM(cgCustID,cgGSMNo,cgReqType,cgUpdateD,cgbank) values (@CUST_NO_N,@CUST_MOBTELNO_N,'PS',getdate(),'NBD')
Print 'am in custgsm'
-- IF @@ERROR <> 0
-- GOTO ERROR_HANDLERR;
END
if @FUNC_ID_N = 1
BEGIN
set @servCode = '100'
set @psabtime = '17'
if (@NOTIFY_TYPE_C = 'W')
BEGIN

if (@NOTIFY_ON_C='THU')
BEGIN
set @psabweekly = '05'
set @psabmonthly='00'
END
else
BEGIN
set @psabweekly = '07'
set @psabmonthly='00'
END
END
if (@NOTIFY_TYPE_C = 'M' )
BEGIN
set @psabmonthly='31'
set @psabweekly = '00'

END
END

if @FUNC_ID_N = 2
BEGIN
set @servCode = '700'
set @psabtime = '12'
if (@NOTIFY_TYPE_C = 'W')
BEGIN
if (@NOTIFY_ON_C='THU')
BEGIN
set @psabweekly = '05'
set @psabmonthly='00'
END
else
BEGIN
set @psabweekly = '07'
set @psabmonthly='00'
END
END
if (@NOTIFY_TYPE_C = 'M' )
BEGIN
set @psabweekly = '00'
set @psabmonthly='31'
END
END
if @FUNC_ID_N = 4
BEGIN
set @servCode = '702'
set @psabtime = '14'
END

if @FUNC_ID_N = 9
BEGIN
set @servCode = '701'
set @psabtime = '11'
END

-- BEGIN
-- IF NOT EXISTS(SELECT psCustid from pushsubscription where psCustid=@CUST_NO_N and psAccount=@ARNG_NO_V and psServCode=@servCode )
-- BEGIN
if (@FUNC_ID_N = 9 or @FUNC_ID_N = 4)
BEGIN
insert into pushsubscription(psCustid,psservcode,psisab,psabmonthly,psabfortnightly,psabweekly,psabdaily,psabtime,psaccount,psisdefault,psgsmno,psCreatedBy,psCreatedDtTm,psMsgLanguage,psbank)
values(@CUST_NO_N,@servCode,'','','','','',@psabtime,@ARNG_NO_V,'1',@CUST_MOBTELNO_N,'MIGRATED',@REGN_DT_D,'EN','NBD')
Print 'am in 9 or 4'
END

if (@FUNC_ID_N = 1 or @FUNC_ID_N = 2)
BEGIN
if (@NOTIFY_TYPE_C = 'F' )
BEGIN
insert into pushsubscription(psCustid,psservcode,psisab,psabmonthly,psabfortnightly,psabweekly,psabdaily,psabtime,psaccount,psisdefault,psgsmno,psCreatedBy,psCreatedDtTm,psMsgLanguage,psbank)
values(@CUST_NO_N,@servCode,@ARNG_TYPE_C,'00','15','00','00',@psabtime,@ARNG_NO_V,'1',@CUST_MOBTELNO_N,'MIGRATED',@REGN_DT_D,'EN','NBD')
insert into pushsubscription(psCustid,psservcode,psisab,psabmonthly,psabfortnightly,psabweekly,psabdaily,psabtime,psaccount,psisdefault,psgsmno,psCreatedBy,psCreatedDtTm,psMsgLanguage,psbank)
values(@CUST_NO_N,@servCode,@ARNG_TYPE_C,'00','31','00','00',@psabtime,@ARNG_NO_V,'1',@CUST_MOBTELNO_N,'MIGRATED',@REGN_DT_D,'EN','NBD')
Print 'am in fortnight'
END
else
BEGIN
insert into pushsubscription(psCustid,psservcode,psisab,psabmonthly,psabfortnightly,psabweekly,psabdaily,psabtime,psaccount,psisdefault,psgsmno,psCreatedBy,psCreatedDtTm,psMsgLanguage,psbank)
values(@CUST_NO_N,@servCode,@ARNG_TYPE_C,@psabmonthly,'00',@psabweekly,'00',@psabtime,@ARNG_NO_V,'1',@CUST_MOBTELNO_N,'MIGRATED',@REGN_DT_D,'EN','NBD')
Print 'am in monthly or weekly'
END
END
-- END
-- END

update v_customer_nbd set migrt_status='0',Mig_error='',migrtd_time=getdate() where CUST_NO_N=@CUST_NO_N

END TRY
BEGIN CATCH
-- BEGIN
Print 'am inside error handlerr'
Print @CUST_NO_N
Print @@ERROR
ROLLBACK TRANSACTION
-- if @@ERROR <> 0
-- BEGIN
SELECT @ErrMsg = ERROR_MESSAGE()
Print @ErrMsg
update v_customer_nbd set migrt_status='1',Mig_error=@ErrMsg,migrtd_time=getdate() where CUST_NO_N=@CUST_NO_N
-- END
-- END
END CATCH
COMMIT TRANSACTION
Print 'before fetching next'
fetch next from @datacol into @CUST_NO_N, @CUST_NAME_V, @CUST_ADDR1_V,
@CUST_ADDR2_V, @CUST_ADDR3_V, @CUST_RESTELNO_N, @CUST_OFFTELNO_N, @CUST_EMAILADDR_V,
@CUST_SETUPDT_D, @CUST_MOBTELNO_N, @FUNC_ID_N, @ARNG_NO_V, @ARNG_TYPE_C, @REGN_DT_D, @NOTIFY_TYPE_C, @NOTIFY_ON_C
Print 'after fetching next'

END

close @datacol
deallocate @datacol

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources