Can anybody guide me, i'm inserting DataTable in database, using type table, at the same time i'm returning the 2 fields. The issue i'm suffering with is if there are 7 entries in DataTable to be inserted, then 14 entries will be inserted.
I'm i doing it wrong? what could be the right way to do this... Return is perfectly working... issue is just inserting double the num of rows in dt to be inserted
- ALTER PROCEDURE [dbo].[proc_InsertStore_Recvry]
- (@dt_Recovery Recovery_Store READONLY)
- AS
- Declare @RecoveryIDs as Table (IDs int, ClientIds int)
- declare @StoreID int
- declare @ClientID int
- declare @Arrears decimal(18, 2)
- declare @NetDues decimal(18, 2)
- declare @Received decimal(18, 2)
- Declare @RecoveryRecID int
- begin
- select * into #tempTable from @dt_Recovery
- declare @Count int
- set @Count= (select COUNT(*) from #tempTable)
- while(@Count > 0)
- begin
- set @Count = @Count-1
- set @ClientID = (Select top 1 ClientID from #tempTable)
- set @StoredID = (Select top 1 StoredID from #tempTable where ClientID=@ClientID)
- set @Arrears = (Select top 1 Arrears from #tempTable where ClientID=@ClientID)
- set @NetDues = (Select top 1 NDues from #tempTable where ClientID=@ClientID)
- set @Received = (Select top 1 Received from #tempTable where ClientID=@ClientID)
- Insert into tblRecovery (StoreID, ClientID, Arrears, NetDues, Received)
- values (@StoreID,@ClientID,@Arrears,@NetDues,@Received)
- select @RecoveryID = Scope_Identity()
- insert into @RecoveryIDs (IDs,ClientIds) values (@RecoveryID, @ClientID )
- delete from #tempTable where ClientID=@ClientID
- end
- Select * from @RecoveryIDs
Laxmidhar SahooPosted Oct 20, 2018, 6:37 AM