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
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
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