|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-12 : 08:05:34
|
| Mattias writes "This procedure runs to remove the responible person for Accounts and a child Accounts. We get the specific Accounts from the table EIM_ACCOUNT. We only pick the Accounts that allready are in Siebel, then we get the child Accounts that belongs to every Account by using the PAR_OU_ID column.We get values from three tables in Siebel - S_ORG_EXT, S_POSTN och S_ORG_INT.Our problem - When we run this procedure one of the cursors doesn´t run correct. This cursor suddenly starts collecting Accounts from the beginning of the basetable S_ORG_EXT and runs through all Accounts in the entire base table (45 000 Accounts).This means that we delete the responsible person for all Accounts in the Database.... Not good. Please help us...------------------------------------------------------------CREATE PROCEDURE sp_PE_Kundansvarig_Delete ASDECLARE @ROW_ID varchar(20), @LOC2 varchar(30), @PAR_OU_ID varchar(30), @AnlStatus varchar(30)DECLARE @AnlLOC varchar(20), @KundLOC varchar(30), @KundNAME varchar(100), @KunStatus varchar(30), @KundLOC1 varchar(30), @KundNr varchar(20), @Finns varchar(20)DECLARE @KUNDID varchar(255)DECLARE @ACCNT_BU varchar(255)DECLARE @NAME varchar(255)DECLARE @LOC varchar(255)DECLARE @BU_ID varchar(255)DECLARE @OU_EXT_ID varchar(255)DECLARE @POSITION_ID varchar(255)DECLARE @ROW_STATUS varchar(255)DECLARE @ACCNT_LOC varchar(255)DECLARE @POSTN_NAME varchar(255)DECLARE @TEST varchar(255)DECLARE @POSTN_DIVN varchar(255)DECLARE @Sec varchar(30)drop table TEMP_ANSVARIGCREATE TABLE TEMP_ANSVARIG (ROW_ID int identity(8,10), IF_ROW_BATCH_NUM varchar(3)NULL, IF_ROW_STAT varchar(10)NULL, Affärsenhet varchar(30)NULL, BU varchar(30)NULL, NAME varchar(50)NULL, LOC varchar(30)NULL, Position varchar(100)NULL, POSTN_NAME varchar(30)NULL, POSTN_DIVN varchar(30)NULL,) DECLARE Ansvarig_Cursor CURSOR FOR SELECT LOC, NAMEFROM EIM_ACCOUNT WHERE IF_ROW_BATCH_NUM = '100' AND LOC IN (Select LOC from S_ORG_EXT) AND NAME IN (Select NAME from S_ORG_EXT)OPEN Ansvarig_CursorFETCH NEXT FROM Ansvarig_Cursor INTO @KundLOC, @KundNameWHILE @@FETCH_STATUS = 0BEGIN -- För varje Kund hämtas Kundnr i S_ORG_EXT SET @ROW_ID = (SELECT ROW_ID FROM S_ORG_EXT where LOC = @KundLOC AND NAME = @KundName) DECLARE Ansvarig_Cursor2 CURSOR FOR Select S_ORG_EXT.BU_ID, NAME, LOC , PR_POSTN_ID from S_ORG_EXT Where ROW_ID = @ROW_ID OR PAR_OU_ID = @ROW_ID AND @ROW_ID IN (Select PAR_OU_ID from S_ORG_EXT) OPEN Ansvarig_Cursor2 FETCH NEXT FROM Ansvarig_Cursor2 INTO @BU_ID, @NAME, @LOC, @POSITION_ID WHILE @@FETCH_STATUS = 0 BEGIN DECLARE Ansvarig_Cursor3 CURSOR FOR SELECT NAME from S_POSTN where ROW_ID = @POSITION_ID OPEN Ansvarig_Cursor3 FETCH NEXT FROM Ansvarig_Cursor3 INTO @POSTN_NAME WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM Ansvarig_Cursor3 INTO @POSTN_NAME END CLOSE Ansvarig_Cursor3 DEALLOCATE Ansvarig_Cursor3 DECLARE Ansvarig_Cursor4 CURSOR FOR SELECT S_ORG_INT.NAME from S_ORG_INT, S_POSTN where S_ORG_INT.ROW_ID = S_POSTN.OU_ID AND @POSITION_ID = S_POSTN.ROW_ID OPEN Ansvarig_Cursor4 FETCH NEXT FROM Ansvarig_Cursor4 INTO @POSTN_DIVN WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM Ansvarig_Cursor4 INTO @POSTN_DIVN END CLOSE Ansvarig_Cursor4 DEALLOCATE Ansvarig_Cursor4 INSERT INTO TEMP_ANSVARIG (IF_ROW_BATCH_NUM, IF_ROW_STAT, Affärsenhet, BU, NAME, LOC, Position, POSTN_NAME, POSTN_DIVN) SELECT '190', 'FOR_DELETE', 'Plusenergi AB', @BU_ID, @NAME, @LOC, @POSITION_ID, @POSTN_NAME, @POSTN_DIVN FETCH NEXT FROM Ansvarig_Cursor2 INTO @BU_ID, @NAME, @LOC, @POSITION_ID |
|
Cursors 