Deleting multiple records from a datagrid using cursors

[Ganesh R]

Hi Friends...
Can anyone plz help me out i'm stuck ....
I want to delete multiple records from a data grid using cursors.the parameter for the stored procedure is an comma seperated string(of User_ID),but the stored procedure which i had written is not working can anyone help me out,it would be a great help for me.when i execute the procedure and print @@FETCH_STATUS the status it prints -1 ie no record ....

CREATE PROCEDURE test_cur3(@User_ID varchar(100))
as

SET NOCOUNT ON

declare @UID char(15)
DECLARE TESTCUR3 CURSOR for
SELECT User_ID FROM Header WHERE User_ID in ( @User_ID)

open TESTCUR3

FETCH NEXT FROM TESTCUR3
INTO @UID
WHILE @@FETCH_STATUS = 0
BEGIN


update Header set Deleted='1' WHERE User_ID= @UID


FETCH NEXT FROM TESTCUR3
INTO @UID
END;

CLOSE TESTCUR3
DEALLOCATE TESTCUR3
GO

[davef]

How many User IDs are you trying to send? My first guess is that your sp could possibly work with a single ID. I understand that you concatenate multiple User IDs in a comma delimited string along the lines of '1,2,3,4'. That becomes your input parameter, which the query is unable to match:

SELECT User_ID FROM Header WHERE User_ID IN ('1,2,3,4')

Massage the parameter to modify your query (if you haven't done so yet):

SELECT User_ID FROM Header WHERE User_ID IN ('1','2','3','4')

[Ganesh R]

Hi...

Thnks for your reply,but i had already tried to Massage the parameter to modify the query
SELECT User_ID FROM Header WHERE User_ID IN ('1','2','3','4')

but still it's not working, can u plz suggest me some other way to solve this problem.

[amitpatel66]

Hi...

Thnks for your reply,but i had already tried to Massage the parameter to modify the query
SELECT User_ID FROM Header WHERE User_ID IN ('1','2','3','4')

but still it's not working, can u plz suggest me some other way to solve this problem.

Have you tried by passing a single value?

Check if that is working and POST back. If the procedure is working with single value then we could trace out if the problem is with multiple value being passed or with the procedure itself.

[amitpatel66]

Hi Friends...
Can anyone plz help me out i'm stuck ....
I want to delete multiple records from a data grid using cursors.the parameter for the stored procedure is an comma seperated string(of User_ID),but the stored procedure which i had written is not working can anyone help me out,it would be a great help for me.when i execute the procedure and print @@FETCH_STATUS the status it prints -1 ie no record ....

CREATE PROCEDURE test_cur3(@User_ID varchar(100))
as

SET NOCOUNT ON

declare @UID char(15)
DECLARE TESTCUR3 CURSOR for
SELECT User_ID FROM Header WHERE User_ID in ( @User_ID)

open TESTCUR3

FETCH NEXT FROM TESTCUR3
INTO @UID
WHILE @@FETCH_STATUS = 0
BEGIN


update Header set Deleted='1' WHERE User_ID= @UID


FETCH NEXT FROM TESTCUR3
INTO @UID
END;

CLOSE TESTCUR3
DEALLOCATE TESTCUR3
GO

Is yur code displaying any error??
I think you missed COMMIT in the procedure if it is not throwing any error?

[Ganesh R]

Hi..

As u suggested I tried by passing a single value ,but still it's not working and even my code is not displaying any error ...I'm not able to undersatand where exactly i had made the mistake...

[davef]

Hi..

As u suggested I tried by passing a single value ,but still it's not working and even my code is not displaying any error ...I'm not able to undersatand where exactly i had made the mistake...

Put a Print statement after

SELECT User_ID FROM Header WHERE User_ID in ( @User_ID)

and run this parsed query in the analyzer or Mgmt Studio. See if it fetches any results.

[amitpatel66]

Hi Friends...
Can anyone plz help me out i'm stuck ....
I want to delete multiple records from a data grid using cursors.the parameter for the stored procedure is an comma seperated string(of User_ID),but the stored procedure which i had written is not working can anyone help me out,it would be a great help for me.when i execute the procedure and print @@FETCH_STATUS the status it prints -1 ie no record ....

CREATE PROCEDURE test_cur3(@User_ID varchar(100))
as

SET NOCOUNT ON

declare @UID char(15)
DECLARE TESTCUR3 CURSOR for
SELECT User_ID FROM Header WHERE User_ID in ( @User_ID)

open TESTCUR3

FETCH NEXT FROM TESTCUR3
INTO @UID
WHILE @@FETCH_STATUS = 0
BEGIN


update Header set Deleted='1' WHERE User_ID= @UID


FETCH NEXT FROM TESTCUR3
INTO @UID
END;

CLOSE TESTCUR3
DEALLOCATE TESTCUR3
GO

First of all Check yur data if it exist that you are trying to UPDATE.

Probably you can try executing a function with some print statements to check what is the value passed as input parameter and whether the CURSOR is getting executed properly!!

[Ganesh R]

if i pass the parameter in the following format it does return result but still the update fails
SELECT User_ID FROM Header WHERE User_ID in ( '1000010001','1000010002')

[davef]

if i pass the parameter in the following format it does return result but still the update fails
SELECT User_ID FROM Header WHERE User_ID in ( '1000010001','1000010002')

The chances are that you mess up with multiple User IDs in the IN clause. Your sp works perfectly fine with a single parameter. Here's a solution:

1. Create an auxiliary split function like this:

CREATE FUNCTION dbo.Split
(
    @RowData nvarchar(2000),
    @SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
    Id int identity(1,1),
    Data nvarchar(100)
) 
AS  
BEGIN 
    Declare @Cnt int
    Set @Cnt = 1
 
    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select 
            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
 
        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End
 
    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))
 
    Return
END
 
 

2. Modify your sp:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[test_cur3](@User_ID varchar(100)) 
as
 
SET NOCOUNT ON
 
declare @UID char(15)
DECLARE TESTCUR3 CURSOR for
 SELECT [User_ID] FROM Header WHERE [User_ID] in ( select Data from dbo.Split(@User_ID, ','))
 
open TESTCUR3
 
FETCH NEXT FROM TESTCUR3
INTO @UID 
WHILE @@FETCH_STATUS = 0
BEGIN
 
 
update Header set Deleted='1' WHERE [User_ID]= @UID 
 
 
FETCH NEXT FROM TESTCUR3
INTO @UID
END;
 
CLOSE TESTCUR3
DEALLOCATE TESTCUR3
 

3. Call your sp:

exec test_cur3 '1000010001,1000010002,1000010003'

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

[balajiu]

Hi Friends...
Can anyone plz help me out i'm stuck ....
I want to delete multiple records from a data grid using cursors.the parameter for the stored procedure is an comma seperated string(of User_ID),but the stored procedure which i had written is not working can anyone help me out,it would be a great help for me.when i execute the procedure and print @@FETCH_STATUS the status it prints -1 ie no record ....

CREATE PROCEDURE test_cur3(@User_ID varchar(100))
as

SET NOCOUNT ON

declare @UID char(15)
DECLARE TESTCUR3 CURSOR for
SELECT User_ID FROM Header WHERE User_ID in ( @User_ID)

open TESTCUR3

FETCH NEXT FROM TESTCUR3
INTO @UID
WHILE @@FETCH_STATUS = 0
BEGIN


update Header set Deleted='1' WHERE User_ID= @UID


FETCH NEXT FROM TESTCUR3
INTO @UID
END;

CLOSE TESTCUR3
DEALLOCATE TESTCUR3
GO

If you pass argument like '1,2,3,4' , the database will assume input as single element and it won't assume that the input is a collection of elements seperated by comma,

Please pass input like the above and user defined split method to split the elements.

[Ganesh R]

thnks a lot for your reply ...i had tried with split funtion and it's working ,but was trying without using split funtion ,thought there might be some other way out...

anyway's thnks a lot guys for ur help .....