Problem with Cursor on SP??

[MeGGaN]

Please please help!!

I have a stored procedure that inserts records on a table based on the records inserted on a table from another DB. It executed from a VB application.

Inside the SP I declare a cursor like this:

DECLARE LINES CURSOR FOR
SELECT MESTR_FOLIO,
PRSTR_COD,
MPINT_PARTIDA,
MPFLO_CANTIDAD_SOL,
UNSTR_COD_BASE
FROM SUCURSALES_prueba.dbo.AIn_MovsPar_Tr
WHERE MESTR_FOLIO = @MESTR_FOLIO
AND MPFLO_CANTIDAD_SOL > 0

OPEN PARTIDAS

FETCH NEXT FROM LINES INTO @MESTR_FOLIO, @PRSTR_COD, @MPINT_PARTIDA, @MPFLO_CANTIDAD_SOL, @UNSTR_COD_BASE

WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS = 0 )
BEGIN
-- INSERT on SOSHIPLINE

insert soshipline ( fields...)
) values( values and variables for the fields)

CLOSE LINES
DEALLOCATE LINES

Of course I handle errors and stuff and the actual code is quite bigger. The thing is that it works fine on my test and production DB but it has happened about 5-10 times (can happen more) that the records inserted on soshipline are different from the records on the other table.

I don't know why is this happening and also I couldn't define the circunstances when this happens because every time it happened, I had different results: Sometimes I get extra records on the 2nd table (3 instead of 1 or so), sometimes I get less records on the 2nd table (1 instead of 4) and there are times when I get the exact same number of records (ex. 1) but the article on the line is TOTALLY different on both tables and has not been used on previous records, and the code is not alike or anything else.

The stored can be executed by several users at the same time through a VB application.

Is there a chance that the cursor is allocating additional records or selecting extra records from the first table? (How can my select statement be wrong??)

How could I detect what is wrong? I ran the stored procedure on a test DB for the records that &quot;failed&quot; or that were wrong and the records were inserted correctly but the test DB has not the same conditions begining from it is not been used concurrently by many users...

I hope anyone can help me!



________________
Magda Banuet

 

[hmscott]

I guess my first question would be, why do you need a cursor here? It looks like a pretty straight forward INSERT statement would suffice (and cursors should be avoided where possible).

2nd, in the example you gave, you

DECLARE LINES CURSOR FOR ...

but you

OPEN PARTIDAS

was that a misprint, or is that part of the problem?

3rd, for diagnostics, I would use SQL Profiler on my test box and run a the SP multiple times (from different clients if possible, using different connection names). Watch the results and see if there is some interaction that ought not be occurring.

best of luck,

hmscott

 

[MeGGaN]

Hi!, Well OPEN PARTIDAS was a misprint

I use a cursor because I don't know how many rows I will have and I have to do an insert for every row readed. (I'm using a while loop)

How can I read one record, assing the variables for that record, do the insert and move to the next record without using a cursor?? Do you have any alternative example?

________________
Magda Banuet

 

[hmscott]

Try this (as an example only).

-- Create table a
create table a (
value1 varchar(10) null,
value2 int null
)
go

-- Create identical table b
create table b (
value1 varchar(10) null,
value2 int null
)
go


-- Move 4 records into table b
insert b (value1, value2)
select 'one', 1 union all
select 'two', 2 union all
select 'tres', 3 union all
select 'cuatro', 4
go

/* This example moves multiple records from table b
to table a with a single SQL Statement */
insert a (value1, value2)
select *
from b
go

 

[bdreed35]

Can't you just do an INSERT INTO...SELECT

INSERT INTO SOSHIPLINE
      ( MESTR_FOLIO,
        PRSTR_COD,
        MPINT_PARTIDA,
        MPFLO_CANTIDAD_SOL,
        UNSTR_COD_BASE
      )
SELECT  MESTR_FOLIO,
        PRSTR_COD,
        MPINT_PARTIDA,
        MPFLO_CANTIDAD_SOL,
        UNSTR_COD_BASE
    FROM     SUCURSALES_prueba.dbo.AIn_MovsPar_Tr
    WHERE     MESTR_FOLIO = @MESTR_FOLIO
        AND MPFLO_CANTIDAD_SOL > 0

~Brian

 

[MeGGaN]

Interesting what hmscott says... but I'm not sure if I can do that for my case.

You know, the 2 tables are not equal. I loop because I have to select data from the first table, store it on variables so I can use it to select more data from another 3 tables and then, when I have the data for all the columns of the 2nd table, I do the insert. I then repeat the process for all the records on the first table.



________________
Magda Banuet

 

[hmscott]

Magda,

There are several individuals in this forum and other who believe wholeheartedly that cursors are one of life's cardinal sins. I'm not one of them, but they do have a point (complexity, performance, etc). Consider this alternate solution. It doesn't directly address your situation, but it might give you an idea for how to manipulate data into the desired structure without using a cursor.

By the way, my initial recommendation (to use SQL Profiler and run the SP from multiple clients) still stands even when using this solution.


CREATE @Temp_Table (
MyValue varchar(10),
MyData int,
OtherData int)
GO

INSERT @Temp_Table (MyValue)
SELECT Value FROM FirstTable
GO

UPDATE @Temp_Table
SET MyData = B.Data
FROM @Temp_Table JOIN SecondTable B ON
@Temp_Table.MyValue = B.Value
GO

UPDATE @Temp_Table
SET OtherData = C.OtherData
FROM @Temp_Table JOIN ThirdTable C ON
@Temp_Table.MyValue = C.Value
GO

INSERT FirstTable (Value, Data, OtherData)
SELECT MyValue, Mydata, OtherData
FROM @Temp_Table
GO

DROP TABLE @Temp_Table
GO

 

[MeGGaN]

I definitely think it is a better solution and easier to understand (I found the cursors to be a headache when trying to fetch next record succesfully).

I think I will change it all to work this way: I would do an insert on the temp table and update it with the results from further selects based on that record. When I have the record ready, I would do the insert from the temp table (and maybe I can insert all the records at once the way you told me on your first solution).

Thank you so much



________________
Magda Banuet

 

[hmscott]

Your very welcome. I hope it works well for you.

Nigel, another convert for you.

hmscott

 

[bdreed35]

Sorry if I am missing something here, but can you join your 4 tables together in on select statement? Again, I am just asking since it doesn't seem to have been brought up.

SELECT 
      A.ID,
      B.FIELD1,
      C.FIELD2,
      D.FIELD3
FROM 
     TABLE1 A 
       INNER JOIN TABLE2 B ON A.ID = B.ID
       INNER JOIN TABLE3 C ON A.ID = C.ID
       INNER JOIN TABLE4 D ON A.ID = D.ID

You could even insert this right into your final table.

~Brian

 

[MeGGaN]

I don't link them because I would have a very complex statement (I do a lot of filtering on each separate select) and I prefer simple and fast queries vs huge join statements that sometimes doesn't return the result I expect it to or takes too long to run for the number of records I have to scan.

Also, If some of the tables doesn't have a match (happens a lot), the resultset might end with no records and it is really hard to trace what field was missing and a full join is not of great help to me sometimes.



________________
Magda Banuet