Click here to Skip to main content
15,608,652 members
Please Sign up or sign in to vote.
2.50/5 (2 votes)
Hello everyone,

I am having a stored procedure in which i have used cursor, the cursor loops works fine but it doesn't insert last record please tell me how to resolve this problem.

My Stored procedure :

GO
/****** Object:  StoredProcedure [dbo].[sp_InsertFinance]    Script Date: 08/08/2012 18:51:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_InsertFinance]
	@schoolid nvarchar(max),
	@createdby int,
	@financedate nvarchar(max),
	@remarks nvarchar(max),
	@ismailed bit
AS
BEGIN
	
	SET NOCOUNT ON;
	declare @message nvarchar(20)
	declare @fschoolid int
	declare @ffinancedate nvarchar(max)
	declare @fremarks nvarchar(max)
	--school cursor
	declare sc_cursor cursor for 
	select * from uf_csvtotable(@schoolid)
	open sc_cursor
	fetch next from sc_cursor into @fschoolid
	
	--date cursor
	declare dt_cursor cursor for
	select *from uf_csvtotable(@financedate)
	open dt_cursor
	fetch next from dt_cursor into @ffinancedate
	--select @ffinancedate 
	--remarks cursor
	declare re_cursor cursor for
	select * from uf_csvtotable(@remarks)
	open re_cursor
	fetch next from re_cursor into @fremarks
	--select @fremarks 
	--select @@FETCH_STATUS 
	--select @ffinancedate
	--loop begins
	while @@FETCH_STATUS =0
	begin
	
	select @@FETCH_STATUS as 'Status'
		begin try
		insert into tbl_Finance (schoolid,FinanceDate,isMailed,isDeleted,Remarks,CreatedBy,CreatedDateTime )
		values(@fschoolid,convert(date,@ffinancedate),@ismailed,'false',@fremarks,@createdby,GETDATE())
		set @message ='Yes'
		end try
		begin catch
		set @message ='No'
		end catch
		fetch next from sc_cursor into @fschoolid
		fetch next from dt_cursor into @ffinancedate
		fetch next from re_cursor into @fremarks
	end
	
	close sc_cursor
	close dt_cursor
	close re_cursor
	deallocate sc_cursor
	deallocate dt_cursor
	deallocate re_cursor
	select @message as 'Message'
	
    
END


I am passing value from asp.net page i.e.

@schoolid -2,4,5,3,1
@createdby -1
@financedate - 2012-08-08,2012-08-08,2012-08-08,2012-08-08,2012-08-08
@remarks - , , , , (i.e all parameters are blank)
@ismailed -false

now my UDF is :
GO
/****** Object:  UserDefinedFunction [dbo].[UF_CSVToTable]    Script Date: 08/08/2012 19:01:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[UF_CSVToTable]
(
 @psCSString VARCHAR(max)
)
RETURNS @otTemp TABLE(sID VARCHAR(2000))
AS
BEGIN
 DECLARE @sTemp VARCHAR(max)

 WHILE LEN(@psCSString) > 0
 BEGIN
  SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),
                    LEN(@psCSString)))
  SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),
                               LEN(@psCSString)) + 1, LEN(@psCSString))
  INSERT INTO @otTemp VALUES (@sTemp)
 END

RETURN
END


it inserts records 2,4,5,3 but doesnt inserts 1 please tell me why and how to resolve it..i am really stuck up..

Regards,
Krunal
Posted
Comments
scottgp 8-Aug-12 13:11pm    
It seems to work ok for me. I took a guess at the definition of the table:

CREATE TABLE [dbo].[tbl_Finance](
[schoolid] [int] NULL,
[FinanceDate] [date] NULL,
[isMailed] [varchar](20) NULL,
[isDeleted] [varchar](20) NULL,
[Remarks] [varchar](50) NULL,
[CreatedBy] [int] NULL,
[CreatedDateTime] [datetime] NULL
) ON [PRIMARY]
, created the function and stored procedure, and it inserted all five rows.
[no name] 10-Aug-12 9:25am    
Hi scottgp,

I have tried it many times it didnt worked anytime, i found the problem ; it was with remarks column when i insert it blank then it skips the last row, but when i enter something in last row then it gets inserted into the database. Now i dont know why this thing occured...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900