You’re offline. This is a read only version of the page.
280
I have code in the rmCustomerModifierPre that might occasionally throw an exception for various reasons. I have found that if it does so, the rmCustomerModifier isn't structured properly and what happens is it jumps to the part where the indexes are supposed to get recreated, but since they have never been dropped, another exception is thrown.
I get the following error in one of my applications that is calling this econnect sproc. "
System.Data.SqlClient.SqlException: 'There is already an object named 'rm_customer_mstr_na' in the database.
Could not create constraint or index. See previous errors.'"
If it was working properly I would see the exception that occurred in the rmCustomerModifierPre and the proc wouldn't be trying to recreate an index that is already there...
unfortunately after formatting the proc, I see that the error trapping is some midevil mess....SQL Server does have try catch now which would make it easier to read and probably work better.
I am going to try to fix this...here is the original, I'll come back later with what I think works if I am lucky...
Share
ReportUSE [INV]
GO
/****** Object: StoredProcedure [dbo].[rmCustomerModifier] Script Date: 2/5/2018 7:52:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[rmCustomerModifier] @I_charStartCustomer VARCHAR(30),
@I_charEndCustomer VARCHAR(30),
@O_iErrorState INT = NULL
output
AS
SET TRANSACTION isolation level READ uncommitted
SET nocount ON
DECLARE @LEN1 TINYINT,
@LEN2 TINYINT,
@BALNCTYP INT,
@CUSTNMBR CHAR(255),
@tTransaction TINYINT,
@tLoop TINYINT,
@iStatus INT,
@cStartCustomer CHAR(50),
@cEndCustomer CHAR(50)
SELECT @LEN1 = 0,
@LEN2 = 0,
@BALNCTYP = 0
EXEC @iStatus = Smformatstringsforexecs
@I_vInputString = @I_charStartCustomer,
@O_cOutputString = @cStartCustomer output,
@O_iErrorState = @O_iErrorState output
IF @iStatus <> 0
OR @O_iErrorState <> 0
BEGIN
SET @O_iErrorState = 90
RETURN
END
EXEC @iStatus = Smformatstringsforexecs
@I_vInputString = @I_charEndCustomer,
@O_cOutputString = @cEndCustomer output,
@O_iErrorState = @O_iErrorState output
IF @iStatus <> 0
OR @O_iErrorState <> 0
BEGIN
SET @O_iErrorState = 90
RETURN
END
SELECT @O_iErrorState = 0
IF @@trancount = 0
BEGIN
SELECT @tTransaction = 1
BEGIN TRANSACTION
END
WHILE ( @tLoop IS NULL )
BEGIN
SELECT @tLoop = 1
EXEC @iStatus = Rmcustomermodifierpre
@I_charStartCustomer output,
@I_charEndCustomer output,
@cStartCustomer output,
@cEndCustomer output,
@O_iErrorState output
IF @O_iErrorState <> 0
BEGIN
BREAK
END
IF @I_charStartCustomer IS NULL
OR @I_charEndCustomer IS NULL
OR @cStartCustomer IS NULL
OR @cEndCustomer IS NULL
BEGIN
SELECT @O_iErrorState = 1
BREAK
END
IF EXISTS (SELECT *
FROM sysobjects
WHERE NAME = 'RM_Customer_MSTR_NA')
ALTER TABLE dbo.rm00101
DROP CONSTRAINT rm_customer_mstr_na
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 2
BREAK
END
IF EXISTS (SELECT *
FROM sysobjects
WHERE NAME = 'RM_NationalAccounts_MSTR_FKC')
ALTER TABLE dbo.rm00105
DROP CONSTRAINT rm_nationalaccounts_mstr_fkc
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 3
BREAK
END
IF EXISTS (SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.taCustomerInsert'))
ALTER TABLE rm00101
disable TRIGGER tacustomerinsert
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 4
BREAK
END
IF EXISTS (SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.taCustomerAddressInsert'))
ALTER TABLE rm00102
disable TRIGGER tacustomeraddressinsert
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 5
BREAK
END
IF EXISTS (SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.tr_SVC_Cust_Ext_U'))
ALTER TABLE rm00102
disable TRIGGER tr_svc_cust_ext_u
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 40
BREAK
END
IF EXISTS (SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.tr_SVC_RM00102_D'))
ALTER TABLE rm00102
disable TRIGGER tr_svc_rm00102_d
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 41
BREAK
END
IF EXISTS (SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.tr_SVC_SVC00601_IUD'))
ALTER TABLE svc00601
disable TRIGGER tr_svc_svc00601_iud
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 42
BREAK
END
SELECT @BALNCTYP = balnctyp
FROM rm00101
WHERE custnmbr = @I_charStartCustomer
IF @BALNCTYP = 1
BEGIN
IF Len(@I_charEndCustomer) > Len(@I_charStartCustomer)
SELECT @LEN1 = ( Len(@I_charEndCustomer) - Len(
@I_charStartCustomer)
)
ELSE IF Len(@I_charEndCustomer) < Len(@I_charStartCustomer)
SELECT @LEN2 = ( Len(@I_charStartCustomer) - Len(
@I_charEndCustomer)
)
UPDATE rm20101
SET custnmbr = @I_charEndCustomer,
docnumbr = Rtrim(@I_charEndCustomer) + '^'
+ Replicate('0', @LEN2)
+ Substring(Rtrim(docnumbr), Len(
@I_charStartCustomer)+2
+
@LEN1, 20)
WHERE custnmbr = @I_charStartCustomer
AND rmdtypal = 0
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 31
BREAK
END
UPDATE rm00401
SET custnmbr = @I_charEndCustomer,
docnumbr = Rtrim(@I_charEndCustomer) + '^'
+ Replicate('0', @LEN2)
+ Substring(Rtrim(docnumbr), Len(
@I_charStartCustomer)+2
+
@LEN1, 20)
WHERE custnmbr = @I_charStartCustomer
AND rmdtypal = 0
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 32
BREAK
END
UPDATE rm30101
SET custnmbr = @I_charEndCustomer,
docnumbr = Rtrim(@I_charEndCustomer) + '^'
+ Replicate('0', @LEN2)
+ Substring(Rtrim(docnumbr), Len(
@I_charStartCustomer)+2
+
@LEN1, 20),
balfwdnm = Rtrim(@I_charEndCustomer) + '^'
+ Replicate('0', @LEN2)
+ Substring(Rtrim(balfwdnm), Len(
@I_charStartCustomer)+2
+
@LEN1, 20)
WHERE custnmbr = @I_charStartCustomer
AND rmdtypal = 0
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 33
BREAK
END
UPDATE rm30101
SET custnmbr = @I_charEndCustomer,
balfwdnm = Rtrim(@I_charEndCustomer) + '^'
+ Replicate('0', @LEN2)
+ Substring(Rtrim(balfwdnm), Len(
@I_charStartCustomer)+2
+
@LEN1, 20)
WHERE custnmbr = @I_charStartCustomer
AND rmdtypal <> 0
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 34
BREAK
END
END
INSERT INTO rm00101
(custnmbr,
custname,
custclas,
cprcstnm,
cntcprsn,
stmtname,
shrtname,
adrscode,
upszone,
shipmthd,
taxschid,
address1,
address2,
address3,
country,
city,
state,
zip,
phone1,
phone2,
phone3,
fax,
prbtadcd,
prstadcd,
staddrcd,
slprsnid,
chekbkid,
pymtrmid,
crlmttyp,
crlmtamt,
crlmtper,
crlmtpam,
curncyid,
ratetpid,
custdisc,
prclevel,
minpytyp,
minpydlr,
minpypct,
fnchatyp,
fnchpcnt,
finchdlr,
mxwoftyp,
mxwrofam,
comment1,
comment2,
userdef1,
userdef2,
taxexmt1,
taxexmt2,
txrgnnum,
balnctyp,
stmtcycl,
bankname,
bnkbrnch,
salsterr,
defcacty,
rmcshacc,
rmaracc,
rmslsacc,
rmivacc,
rmcosacc,
rmtakacc,
rmavacc,
rmfcgacc,
rmwracc,
rmsoracc,
frstindt,
inactive,
hold,
crcardid,
crcrdnum,
ccrdxpdt,
kpdsthst,
kpcalhst,
kperhist,
kptrxhst,
noteindx,
creatddt,
modifdt,
revalue_customer,
post_results_to,
finchid,
govcrpid,
govindid,
disgrper,
duegrper,
docfmtid,
send_email_statements,
userlang,
gpsfointegrationid,
integrationsource,
integrationid,
orderfulfilldefault,
custpriority,
ccode,
declid,
rmovrpymtwrtoffacctidx,
shipcomplete,
cbvat,
includeindp)
SELECT @I_charEndCustomer,
custname,
custclas,
cprcstnm,
cntcprsn,
stmtname,
shrtname,
adrscode,
upszone,
shipmthd,
taxschid,
address1,
address2,
address3,
country,
city,
state,
zip,
phone1,
phone2,
phone3,
fax,
prbtadcd,
prstadcd,
staddrcd,
slprsnid,
chekbkid,
pymtrmid,
crlmttyp,
crlmtamt,
crlmtper,
crlmtpam,
curncyid,
ratetpid,
custdisc,
prclevel,
minpytyp,
minpydlr,
minpypct,
fnchatyp,
fnchpcnt,
finchdlr,
mxwoftyp,
mxwrofam,
comment1,
comment2,
userdef1,
userdef2,
taxexmt1,
taxexmt2,
txrgnnum,
balnctyp,
stmtcycl,
bankname,
bnkbrnch,
salsterr,
defcacty,
rmcshacc,
rmaracc,
rmslsacc,
rmivacc,
rmcosacc,
rmtakacc,
rmavacc,
rmfcgacc,
rmwracc,
rmsoracc,
frstindt,
inactive,
hold,
crcardid,
crcrdnum,
ccrdxpdt,
kpdsthst,
kpcalhst,
kperhist,
kptrxhst,
noteindx,
creatddt,
modifdt,
revalue_customer,
post_results_to,
finchid,
govcrpid,
govindid,
disgrper,
duegrper,
docfmtid,
send_email_statements,
userlang,
gpsfointegrationid,
integrationsource,
integrationid,
orderfulfilldefault,
custpriority,
ccode,
declid,
rmovrpymtwrtoffacctidx,
shipcomplete,
cbvat,
includeindp
FROM rm00101
WHERE custnmbr = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 35
BREAK
END
DELETE rm00101
WHERE custnmbr = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 36
BREAK
END
INSERT INTO rm00102
(custnmbr,
adrscode,
slprsnid,
upszone,
shipmthd,
taxschid,
cntcprsn,
address1,
address2,
address3,
country,
city,
state,
zip,
phone1,
phone2,
phone3,
fax,
modifdt,
creatddt,
gpsfointegrationid,
integrationsource,
integrationid,
ccode,
declid,
locncode,
salsterr,
userdef1,
userdef2)
SELECT @I_charEndCustomer,
adrscode,
slprsnid,
upszone,
shipmthd,
taxschid,
cntcprsn,
address1,
address2,
address3,
country,
city,
state,
zip,
phone1,
phone2,
phone3,
fax,
modifdt,
creatddt,
gpsfointegrationid,
integrationsource,
integrationid,
ccode,
declid,
locncode,
salsterr,
userdef1,
userdef2
FROM rm00102
WHERE custnmbr = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 50
BREAK
END
DELETE rm00102
WHERE custnmbr = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 51
BREAK
END
DECLARE ta_cmcursor insensitive CURSOR FOR
SELECT 'update [' + o.NAME + '] set CUSTNMBR ='
+ Rtrim(@cEndCustomer) + ' where CUSTNMBR = '
+ Rtrim(@cStartCustomer)
FROM sysobjects o,
syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'CUSTNMBR'
AND o.NAME <> 'SOP10100'
AND o.NAME <> 'SOP30200'
AND o.NAME <> 'RM00101'
AND o.NAME <> 'RM00102'
AND o.NAME <> 'DXPublish_Customer'
AND o.NAME <> 'DXPublish_Customer_Work'
AND o.NAME <> 'DXPublish_Address'
AND o.NAME <> 'DXPublish_Address_Work'
ORDER BY o.NAME
SET nocount ON
OPEN ta_cmcursor
FETCH next FROM ta_cmcursor INTO @CUSTNMBR
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 1
BREAK
END
FETCH next FROM ta_cmcursor INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor insensitive CURSOR FOR
SELECT 'update [' + o.NAME + '] set STCUSTID ='
+ Rtrim(@cEndCustomer) + ' where STCUSTID = '
+ Rtrim(@cStartCustomer)
FROM sysobjects o,
syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'STCUSTID'
ORDER BY o.NAME
SET nocount ON
OPEN ta_cmcursor
FETCH next FROM ta_cmcursor INTO @CUSTNMBR
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 6
BREAK
END
FETCH next FROM ta_cmcursor INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor insensitive CURSOR FOR
SELECT 'update [' + o.NAME + '] set ENCUSTID ='
+ Rtrim(@cEndCustomer) + ' where ENCUSTID = '
+ Rtrim(@cStartCustomer)
FROM sysobjects o,
syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'ENCUSTID'
ORDER BY o.NAME
SET nocount ON
OPEN ta_cmcursor
FETCH next FROM ta_cmcursor INTO @CUSTNMBR
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 7
BREAK
END
FETCH next FROM ta_cmcursor INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor insensitive CURSOR FOR
SELECT 'update [' + o.NAME + '] set CPRCSTNM ='
+ Rtrim(@cEndCustomer) + ' where CPRCSTNM = '
+ Rtrim(@cStartCustomer)
FROM sysobjects o,
syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'CPRCSTNM'
ORDER BY o.NAME
SET nocount ON
OPEN ta_cmcursor
FETCH next FROM ta_cmcursor INTO @CUSTNMBR
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 8
BREAK
END
FETCH next FROM ta_cmcursor INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor insensitive CURSOR FOR
SELECT 'update [' + o.NAME
+ '] set CustomerVendor_ID ='
+ Rtrim(@cEndCustomer)
+ ' where SERIES = 3 and CustomerVendor_ID = '
+ Rtrim(@cStartCustomer)
FROM sysobjects o,
syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'CustomerVendor_ID'
AND o.NAME <> 'TX30000'
AND o.NAME <> 'EDCVAT26'
ORDER BY o.NAME
SET nocount ON
OPEN ta_cmcursor
FETCH next FROM ta_cmcursor INTO @CUSTNMBR
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 9
BREAK
END
FETCH next FROM ta_cmcursor INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor insensitive CURSOR FOR
SELECT 'update [' + o.NAME
+ '] set Bill_To_Customer ='
+ Rtrim(@cEndCustomer)
+ ' where Bill_To_Customer = '
+ Rtrim(@cStartCustomer)
FROM sysobjects o,
syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'Bill_To_Customer'
ORDER BY o.NAME
SET nocount ON
OPEN ta_cmcursor
FETCH next FROM ta_cmcursor INTO @CUSTNMBR
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
EXEC (@CUSTNMBR )
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 10
BREAK
END
FETCH next FROM ta_cmcursor INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor insensitive CURSOR FOR
SELECT
'update [' + o.NAME + '] set customer_id =' + ( @cEndCustomer ) +
' where customer_id = ' + Rtrim(@cStartCustomer)
FROM sysobjects o,
syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'customer_id'
ORDER BY o.NAME
SET nocount ON
OPEN ta_cmcursor
FETCH next FROM ta_cmcursor INTO @CUSTNMBR
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 11
BREAK
END
FETCH next FROM ta_cmcursor INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor insensitive CURSOR FOR
SELECT 'update [' + o.NAME
+ '] set Bill_Customer_Number =' + ( @cEndCustomer ) +
' where Bill_Customer_Number = ' + Rtrim(@cStartCustomer)
FROM sysobjects o,
syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'Bill_Customer_Number'
ORDER BY o.NAME
SET nocount ON
OPEN ta_cmcursor
FETCH next FROM ta_cmcursor INTO @CUSTNMBR
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 12
BREAK
END
FETCH next FROM ta_cmcursor INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor insensitive CURSOR FOR
SELECT 'update [' + o.NAME + '] set LINKCODE =' + ( @cEndCustomer )
+
' where PRODTCOD = ''C'' and LINKCODE = ' +
Rtrim(@cStartCustomer)
FROM sysobjects o,
syscolumns c,
syscolumns c2
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'LINKCODE'
AND c.id = c2.id
AND c2.NAME = 'PRODTCOD'
ORDER BY o.NAME
SET nocount ON
OPEN ta_cmcursor
FETCH next FROM ta_cmcursor INTO @CUSTNMBR
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 39
BREAK
END
FETCH next FROM ta_cmcursor INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cccursor insensitive CURSOR FOR
SELECT 'update [' + o.NAME + '] set aaCustID='
+ Rtrim(@cEndCustomer) + ' where aaCustID='
+ Rtrim(@cStartCustomer)
FROM sysobjects o,
syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'aaCustID'
ORDER BY o.NAME
SET nocount ON
OPEN ta_cccursor
FETCH next FROM ta_cccursor INTO @CUSTNMBR
WHILE ( @@FETCH_STATUS <>- 1 )
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 88
BREAK
END
FETCH next FROM ta_cccursor INTO @CUSTNMBR
END
DEALLOCATE ta_cccursor
IF @O_iErrorState <> 0
BREAK
UPDATE a
SET ORMSTRID = @I_charEndCustomer
FROM gl10001 a,
gl10000 b
WHERE a.ormstrid = @I_charStartCustomer
AND a.jrnentry = b.jrnentry
AND b.series = 3
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 13
BREAK
END
UPDATE gl20000
SET ormstrid = @I_charEndCustomer
WHERE ormstrid = @I_charStartCustomer
AND series = 3
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 14
BREAK
END
UPDATE gl30000
SET ormstrid = @I_charEndCustomer
WHERE ormstrid = @I_charStartCustomer
AND series = 3
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 15
BREAK
END
UPDATE pjournal
SET ormstrid = @I_charEndCustomer
WHERE ormstrid = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 16
BREAK
END
UPDATE cm20200
SET cmlinkid = @I_charEndCustomer
WHERE cmlinkid = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 17
BREAK
END
UPDATE cm20300
SET cmlinkid = @I_charEndCustomer
WHERE cmlinkid = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 18
BREAK
END
UPDATE tx30000
SET customervendor_id = @I_charEndCustomer
WHERE customervendor_id = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 218
BREAK
END
IF EXISTS(SELECT 1
FROM sysobjects (nolock)
WHERE NAME = 'EDCVAT26'
AND type = 'U')
BEGIN
UPDATE edcvat26
SET customervendor_id = @I_charEndCustomer
WHERE customervendor_id = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 219
BREAK
END
END
UPDATE mc10000
SET startcustorvendid = @I_charEndCustomer
WHERE startcustorvendid = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 19
BREAK
END
UPDATE mc10000
SET endcustorvendid = @I_charEndCustomer
WHERE endcustorvendid = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 20
BREAK
END
IF EXISTS (SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.PP100100')
AND sysstat & 0xf = 3)
BEGIN
UPDATE pp100100
SET custvndr = @I_charEndCustomer
WHERE custvndr = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 204
BREAK
END
END
IF EXISTS (SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.PA50105')
AND sysstat & 0xf = 3)
BEGIN
UPDATE pa50105
SET custvenid = @I_charEndCustomer
WHERE custvenid = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 21
BREAK
END
END
UPDATE sop10100
SET custnmbr = @I_charEndCustomer
WHERE custnmbr = @I_charStartCustomer
AND prospect = 0
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 22
BREAK
END
UPDATE sop30200
SET custnmbr = @I_charEndCustomer
WHERE custnmbr = @I_charStartCustomer
AND prospect = 0
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 23
BREAK
END
UPDATE sy01200
SET master_id = @I_charEndCustomer
WHERE master_id = @I_charStartCustomer
AND master_type = 'CUS'
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 24
BREAK
END
IF EXISTS (SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.PA01901')
AND sysstat & 0xf = 3)
BEGIN
UPDATE pa01901
SET pacostowner = @I_charEndCustomer
WHERE pacostowner = @I_charStartCustomer
AND ( patrantype = 7
OR patrantype = 10 )
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 25
BREAK
END
END
IF EXISTS (SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.PA43001')
AND sysstat & 0xf = 3)
BEGIN
UPDATE pa43001
SET parecordid = @I_charEndCustomer
WHERE parecordid = @I_charStartCustomer
AND ( pasfid = 21
OR pasfid = 36 )
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 26
BREAK
END
END
IF @O_iErrorState <> 0
BREAK
END
ALTER TABLE dbo.rm00101
WITH NOCHECK ADD CONSTRAINT rm_customer_mstr_na CHECK ((cprcstnm <> '' AND
balnctyp = 0) OR (cprcstnm = '' AND balnctyp = 1) OR (cprcstnm = '' AND
balnctyp = 0))
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 27
END
ALTER TABLE dbo.rm00105
WITH NOCHECK ADD CONSTRAINT rm_nationalaccounts_mstr_fkc FOREIGN KEY (
cprcstnm
) REFERENCES dbo.rm00101 (custnmbr)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 28
END
IF EXISTS (SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.taCustomerInsert'))
ALTER TABLE rm00101
enable TRIGGER tacustomerinsert
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 29
END
IF EXISTS (SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.taCustomerAddressInsert'))
ALTER TABLE rm00102
enable TRIGGER tacustomeraddressinsert
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 30
END
IF EXISTS (SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.tr_SVC_Cust_Ext_U'))
ALTER TABLE rm00102
enable TRIGGER tr_svc_cust_ext_u
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 38
END
IF EXISTS (SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.tr_SVC_RM00102_D'))
ALTER TABLE rm00102
enable TRIGGER tr_svc_rm00102_d
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 43
END
IF EXISTS (SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.tr_SVC_SVC00601_IUD'))
ALTER TABLE svc00601
enable TRIGGER tr_svc_svc00601_iud
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 44
END
EXEC @iStatus = Rmcustomermodifierpost
@I_charStartCustomer,
@I_charEndCustomer,
@cStartCustomer,
@cEndCustomer,
@O_iErrorState
IF @O_iErrorState <> 0
BEGIN
IF @tTransaction = 1
ROLLBACK TRANSACTION
END
ELSE IF @tTransaction = 1
BEGIN
COMMIT TRANSACTION
END
RETURN
*This post is locked for comments
Sorry I forgot to put that into code tags....
All responses (
Answers (
USE [INV]
GO
/****** Object: StoredProcedure [dbo].[rmCustomerModifier] Script Date: 2/5/2018 7:52:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[rmCustomerModifier] @I_charStartCustomer VARCHAR(30)
,@I_charEndCustomer VARCHAR(30)
,@O_iErrorState INT = NULL OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
DECLARE @LEN1 TINYINT
,@LEN2 TINYINT
,@BALNCTYP INT
,@CUSTNMBR CHAR(255)
,@tTransaction TINYINT
,@tLoop TINYINT
,@iStatus INT
,@cStartCustomer CHAR(50)
,@cEndCustomer CHAR(50)
SELECT @LEN1 = 0
,@LEN2 = 0
,@BALNCTYP = 0
EXEC @iStatus = Smformatstringsforexecs @I_vInputString = @I_charStartCustomer
,@O_cOutputString = @cStartCustomer OUTPUT
,@O_iErrorState = @O_iErrorState OUTPUT
IF @iStatus <> 0
OR @O_iErrorState <> 0
BEGIN
SET @O_iErrorState = 90
RETURN
END
EXEC @iStatus = Smformatstringsforexecs @I_vInputString = @I_charEndCustomer
,@O_cOutputString = @cEndCustomer OUTPUT
,@O_iErrorState = @O_iErrorState OUTPUT
IF @iStatus <> 0
OR @O_iErrorState <> 0
BEGIN
SET @O_iErrorState = 90
RETURN
END
SELECT @O_iErrorState = 0
IF @@trancount = 0
BEGIN
SELECT @tTransaction = 1
BEGIN TRANSACTION
END
WHILE (@tLoop IS NULL)
BEGIN
SELECT @tLoop = 1
/* Execute the PRE */
EXEC @iStatus = Rmcustomermodifierpre @I_charStartCustomer OUTPUT
,@I_charEndCustomer OUTPUT
,@cStartCustomer OUTPUT
,@cEndCustomer OUTPUT
,@O_iErrorState OUTPUT
IF @O_iErrorState <> 0
BEGIN
BREAK
END
IF @I_charStartCustomer IS NULL
OR @I_charEndCustomer IS NULL
OR @cStartCustomer IS NULL
OR @cEndCustomer IS NULL
BEGIN
SELECT @O_iErrorState = 1
BREAK
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE NAME = 'RM_Customer_MSTR_NA'
)
ALTER TABLE dbo.rm00101
DROP CONSTRAINT rm_customer_mstr_na
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 2
BREAK
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE NAME = 'RM_NationalAccounts_MSTR_FKC'
)
ALTER TABLE dbo.rm00105
DROP CONSTRAINT rm_nationalaccounts_mstr_fkc
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 3
BREAK
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.taCustomerInsert')
)
ALTER TABLE rm00101 disable TRIGGER tacustomerinsert
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 4
BREAK
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.taCustomerAddressInsert')
)
ALTER TABLE rm00102 disable TRIGGER tacustomeraddressinsert
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 5
BREAK
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.tr_SVC_Cust_Ext_U')
)
ALTER TABLE rm00102 disable TRIGGER tr_svc_cust_ext_u
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 40
BREAK
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.tr_SVC_RM00102_D')
)
ALTER TABLE rm00102 disable TRIGGER tr_svc_rm00102_d
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 41
BREAK
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.tr_SVC_SVC00601_IUD')
)
ALTER TABLE svc00601 disable TRIGGER tr_svc_svc00601_iud
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 42
BREAK
END
SELECT @BALNCTYP = balnctyp
FROM rm00101
WHERE custnmbr = @I_charStartCustomer
IF @BALNCTYP = 1
BEGIN
IF Len(@I_charEndCustomer) > Len(@I_charStartCustomer)
SELECT @LEN1 = (Len(@I_charEndCustomer) - Len(@I_charStartCustomer))
ELSE IF Len(@I_charEndCustomer) < Len(@I_charStartCustomer)
SELECT @LEN2 = (Len(@I_charStartCustomer) - Len(@I_charEndCustomer))
UPDATE rm20101
SET custnmbr = @I_charEndCustomer
,docnumbr = Rtrim(@I_charEndCustomer) + '^' + Replicate('0', @LEN2) + Substring(Rtrim(docnumbr), Len(@I_charStartCustomer) + 2 + @LEN1, 20)
WHERE custnmbr = @I_charStartCustomer
AND rmdtypal = 0
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 31
BREAK
END
UPDATE rm00401
SET custnmbr = @I_charEndCustomer
,docnumbr = Rtrim(@I_charEndCustomer) + '^' + Replicate('0', @LEN2) + Substring(Rtrim(docnumbr), Len(@I_charStartCustomer) + 2 + @LEN1, 20)
WHERE custnmbr = @I_charStartCustomer
AND rmdtypal = 0
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 32
BREAK
END
UPDATE rm30101
SET custnmbr = @I_charEndCustomer
,docnumbr = Rtrim(@I_charEndCustomer) + '^' + Replicate('0', @LEN2) + Substring(Rtrim(docnumbr), Len(@I_charStartCustomer) + 2 + @LEN1, 20)
,balfwdnm = Rtrim(@I_charEndCustomer) + '^' + Replicate('0', @LEN2) + Substring(Rtrim(balfwdnm), Len(@I_charStartCustomer) + 2 + @LEN1, 20)
WHERE custnmbr = @I_charStartCustomer
AND rmdtypal = 0
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 33
BREAK
END
UPDATE rm30101
SET custnmbr = @I_charEndCustomer
,balfwdnm = Rtrim(@I_charEndCustomer) + '^' + Replicate('0', @LEN2) + Substring(Rtrim(balfwdnm), Len(@I_charStartCustomer) + 2 + @LEN1, 20)
WHERE custnmbr = @I_charStartCustomer
AND rmdtypal <> 0
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 34
BREAK
END
END
INSERT INTO rm00101 (
custnmbr
,custname
,custclas
,cprcstnm
,cntcprsn
,stmtname
,shrtname
,adrscode
,upszone
,shipmthd
,taxschid
,address1
,address2
,address3
,country
,city
,STATE
,zip
,phone1
,phone2
,phone3
,fax
,prbtadcd
,prstadcd
,staddrcd
,slprsnid
,chekbkid
,pymtrmid
,crlmttyp
,crlmtamt
,crlmtper
,crlmtpam
,curncyid
,ratetpid
,custdisc
,prclevel
,minpytyp
,minpydlr
,minpypct
,fnchatyp
,fnchpcnt
,finchdlr
,mxwoftyp
,mxwrofam
,comment1
,comment2
,userdef1
,userdef2
,taxexmt1
,taxexmt2
,txrgnnum
,balnctyp
,stmtcycl
,bankname
,bnkbrnch
,salsterr
,defcacty
,rmcshacc
,rmaracc
,rmslsacc
,rmivacc
,rmcosacc
,rmtakacc
,rmavacc
,rmfcgacc
,rmwracc
,rmsoracc
,frstindt
,inactive
,hold
,crcardid
,crcrdnum
,ccrdxpdt
,kpdsthst
,kpcalhst
,kperhist
,kptrxhst
,noteindx
,creatddt
,modifdt
,revalue_customer
,post_results_to
,finchid
,govcrpid
,govindid
,disgrper
,duegrper
,docfmtid
,send_email_statements
,userlang
,gpsfointegrationid
,integrationsource
,integrationid
,orderfulfilldefault
,custpriority
,ccode
,declid
,rmovrpymtwrtoffacctidx
,shipcomplete
,cbvat
,includeindp
)
SELECT @I_charEndCustomer
,custname
,custclas
,cprcstnm
,cntcprsn
,stmtname
,shrtname
,adrscode
,upszone
,shipmthd
,taxschid
,address1
,address2
,address3
,country
,city
,STATE
,zip
,phone1
,phone2
,phone3
,fax
,prbtadcd
,prstadcd
,staddrcd
,slprsnid
,chekbkid
,pymtrmid
,crlmttyp
,crlmtamt
,crlmtper
,crlmtpam
,curncyid
,ratetpid
,custdisc
,prclevel
,minpytyp
,minpydlr
,minpypct
,fnchatyp
,fnchpcnt
,finchdlr
,mxwoftyp
,mxwrofam
,comment1
,comment2
,userdef1
,userdef2
,taxexmt1
,taxexmt2
,txrgnnum
,balnctyp
,stmtcycl
,bankname
,bnkbrnch
,salsterr
,defcacty
,rmcshacc
,rmaracc
,rmslsacc
,rmivacc
,rmcosacc
,rmtakacc
,rmavacc
,rmfcgacc
,rmwracc
,rmsoracc
,frstindt
,inactive
,hold
,crcardid
,crcrdnum
,ccrdxpdt
,kpdsthst
,kpcalhst
,kperhist
,kptrxhst
,noteindx
,creatddt
,modifdt
,revalue_customer
,post_results_to
,finchid
,govcrpid
,govindid
,disgrper
,duegrper
,docfmtid
,send_email_statements
,userlang
,gpsfointegrationid
,integrationsource
,integrationid
,orderfulfilldefault
,custpriority
,ccode
,declid
,rmovrpymtwrtoffacctidx
,shipcomplete
,cbvat
,includeindp
FROM rm00101
WHERE custnmbr = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 35
BREAK
END
DELETE rm00101
WHERE custnmbr = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 36
BREAK
END
INSERT INTO rm00102 (
custnmbr
,adrscode
,slprsnid
,upszone
,shipmthd
,taxschid
,cntcprsn
,address1
,address2
,address3
,country
,city
,STATE
,zip
,phone1
,phone2
,phone3
,fax
,modifdt
,creatddt
,gpsfointegrationid
,integrationsource
,integrationid
,ccode
,declid
,locncode
,salsterr
,userdef1
,userdef2
)
SELECT @I_charEndCustomer
,adrscode
,slprsnid
,upszone
,shipmthd
,taxschid
,cntcprsn
,address1
,address2
,address3
,country
,city
,STATE
,zip
,phone1
,phone2
,phone3
,fax
,modifdt
,creatddt
,gpsfointegrationid
,integrationsource
,integrationid
,ccode
,declid
,locncode
,salsterr
,userdef1
,userdef2
FROM rm00102
WHERE custnmbr = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 50
BREAK
END
DELETE rm00102
WHERE custnmbr = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 51
BREAK
END
DECLARE ta_cmcursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set CUSTNMBR =' + Rtrim(@cEndCustomer) + ' where CUSTNMBR = ' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'CUSTNMBR'
AND o.NAME <> 'SOP10100'
AND o.NAME <> 'SOP30200'
AND o.NAME <> 'RM00101'
AND o.NAME <> 'RM00102'
AND o.NAME <> 'DXPublish_Customer'
AND o.NAME <> 'DXPublish_Customer_Work'
AND o.NAME <> 'DXPublish_Address'
AND o.NAME <> 'DXPublish_Address_Work'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cmcursor
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 1
BREAK
END
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set STCUSTID =' + Rtrim(@cEndCustomer) + ' where STCUSTID = ' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'STCUSTID'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cmcursor
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 6
BREAK
END
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set ENCUSTID =' + Rtrim(@cEndCustomer) + ' where ENCUSTID = ' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'ENCUSTID'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cmcursor
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 7
BREAK
END
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set CPRCSTNM =' + Rtrim(@cEndCustomer) + ' where CPRCSTNM = ' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'CPRCSTNM'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cmcursor
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 8
BREAK
END
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set CustomerVendor_ID =' + Rtrim(@cEndCustomer) + ' where SERIES = 3 and CustomerVendor_ID = ' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'CustomerVendor_ID'
AND o.NAME <> 'TX30000'
AND o.NAME <> 'EDCVAT26'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cmcursor
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 9
BREAK
END
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set Bill_To_Customer =' + Rtrim(@cEndCustomer) + ' where Bill_To_Customer = ' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'Bill_To_Customer'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cmcursor
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 10
BREAK
END
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set customer_id =' + (@cEndCustomer) + ' where customer_id = ' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'customer_id'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cmcursor
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 11
BREAK
END
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set Bill_Customer_Number =' + (@cEndCustomer) + ' where Bill_Customer_Number = ' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'Bill_Customer_Number'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cmcursor
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 12
BREAK
END
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set LINKCODE =' + (@cEndCustomer) + ' where PRODTCOD = ''C'' and LINKCODE = ' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
,syscolumns c2
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'LINKCODE'
AND c.id = c2.id
AND c2.NAME = 'PRODTCOD'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cmcursor
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 39
BREAK
END
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cccursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set aaCustID=' + Rtrim(@cEndCustomer) + ' where aaCustID=' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'aaCustID'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cccursor
FETCH NEXT
FROM ta_cccursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 88
BREAK
END
FETCH NEXT
FROM ta_cccursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cccursor
IF @O_iErrorState <> 0
BREAK
UPDATE a
SET ORMSTRID = @I_charEndCustomer
FROM gl10001 a
,gl10000 b
WHERE a.ormstrid = @I_charStartCustomer
AND a.jrnentry = b.jrnentry
AND b.series = 3
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 13
BREAK
END
UPDATE gl20000
SET ormstrid = @I_charEndCustomer
WHERE ormstrid = @I_charStartCustomer
AND series = 3
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 14
BREAK
END
UPDATE gl30000
SET ormstrid = @I_charEndCustomer
WHERE ormstrid = @I_charStartCustomer
AND series = 3
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 15
BREAK
END
UPDATE pjournal
SET ormstrid = @I_charEndCustomer
WHERE ormstrid = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 16
BREAK
END
UPDATE cm20200
SET cmlinkid = @I_charEndCustomer
WHERE cmlinkid = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 17
BREAK
END
UPDATE cm20300
SET cmlinkid = @I_charEndCustomer
WHERE cmlinkid = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 18
BREAK
END
UPDATE tx30000
SET customervendor_id = @I_charEndCustomer
WHERE customervendor_id = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 218
BREAK
END
IF EXISTS (
SELECT 1
FROM sysobjects(NOLOCK)
WHERE NAME = 'EDCVAT26'
AND type = 'U'
)
BEGIN
UPDATE edcvat26
SET customervendor_id = @I_charEndCustomer
WHERE customervendor_id = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 219
BREAK
END
END
UPDATE mc10000
SET startcustorvendid = @I_charEndCustomer
WHERE startcustorvendid = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 19
BREAK
END
UPDATE mc10000
SET endcustorvendid = @I_charEndCustomer
WHERE endcustorvendid = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 20
BREAK
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.PP100100')
AND sysstat & 0xF = 3
)
BEGIN
UPDATE pp100100
SET custvndr = @I_charEndCustomer
WHERE custvndr = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 204
BREAK
END
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.PA50105')
AND sysstat & 0xF = 3
)
BEGIN
UPDATE pa50105
SET custvenid = @I_charEndCustomer
WHERE custvenid = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 21
BREAK
END
END
UPDATE sop10100
SET custnmbr = @I_charEndCustomer
WHERE custnmbr = @I_charStartCustomer
AND prospect = 0
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 22
BREAK
END
UPDATE sop30200
SET custnmbr = @I_charEndCustomer
WHERE custnmbr = @I_charStartCustomer
AND prospect = 0
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 23
BREAK
END
UPDATE sy01200
SET master_id = @I_charEndCustomer
WHERE master_id = @I_charStartCustomer
AND master_type = 'CUS'
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 24
BREAK
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.PA01901')
AND sysstat & 0xF = 3
)
BEGIN
UPDATE pa01901
SET pacostowner = @I_charEndCustomer
WHERE pacostowner = @I_charStartCustomer
AND (
patrantype = 7
OR patrantype = 10
)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 25
BREAK
END
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.PA43001')
AND sysstat & 0xF = 3
)
BEGIN
UPDATE pa43001
SET parecordid = @I_charEndCustomer
WHERE parecordid = @I_charStartCustomer
AND (
pasfid = 21
OR pasfid = 36
)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 26
BREAK
END
END
IF @O_iErrorState <> 0
BREAK
ALTER TABLE dbo.rm00101
WITH NOCHECK ADD CONSTRAINT rm_customer_mstr_na CHECK (
(
cprcstnm <> ''
AND balnctyp = 0
)
OR (
cprcstnm = ''
AND balnctyp = 1
)
OR (
cprcstnm = ''
AND balnctyp = 0
)
)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 27
END
ALTER TABLE dbo.rm00105
WITH NOCHECK ADD CONSTRAINT rm_nationalaccounts_mstr_fkc FOREIGN KEY (cprcstnm) REFERENCES dbo.rm00101(custnmbr)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 28
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.taCustomerInsert')
)
ALTER TABLE rm00101 enable TRIGGER tacustomerinsert
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 29
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.taCustomerAddressInsert')
)
ALTER TABLE rm00102 enable TRIGGER tacustomeraddressinsert
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 30
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.tr_SVC_Cust_Ext_U')
)
ALTER TABLE rm00102 enable TRIGGER tr_svc_cust_ext_u
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 38
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.tr_SVC_RM00102_D')
)
ALTER TABLE rm00102 enable TRIGGER tr_svc_rm00102_d
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 43
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.tr_SVC_SVC00601_IUD')
)
ALTER TABLE svc00601 enable TRIGGER tr_svc_svc00601_iud
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 44
END
END
/* This is where we end up if an error occurs and BREAK is fired */
EXEC @iStatus = Rmcustomermodifierpost @I_charStartCustomer
,@I_charEndCustomer
,@cStartCustomer
,@cEndCustomer
,@O_iErrorState
IF @O_iErrorState <> 0
BEGIN
IF @tTransaction = 1
ROLLBACK TRANSACTION
END
ELSE IF @tTransaction = 1
BEGIN
COMMIT TRANSACTION
END
RETURN
Ok I tried to keep it is simple as possible
I moved the END which was the END for the while which is where the breaks should take you if there is an error to just after the index and trigger recreation but before the post....Maybe it should be after the post, not sure about that, but I don't get the errors about creating indexes incorrectly when pre throws an error and the error is returned for processing by the sproc.
Here is the corrected version
USE [INV]
GO
/****** Object: StoredProcedure [dbo].[rmCustomerModifier] Script Date: 2/5/2018 7:52:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[rmCustomerModifier] @I_charStartCustomer VARCHAR(30)
,@I_charEndCustomer VARCHAR(30)
,@O_iErrorState INT = NULL OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
DECLARE @LEN1 TINYINT
,@LEN2 TINYINT
,@BALNCTYP INT
,@CUSTNMBR CHAR(255)
,@tTransaction TINYINT
,@tLoop TINYINT
,@iStatus INT
,@cStartCustomer CHAR(50)
,@cEndCustomer CHAR(50)
SELECT @LEN1 = 0
,@LEN2 = 0
,@BALNCTYP = 0
EXEC @iStatus = Smformatstringsforexecs @I_vInputString = @I_charStartCustomer
,@O_cOutputString = @cStartCustomer OUTPUT
,@O_iErrorState = @O_iErrorState OUTPUT
IF @iStatus <> 0
OR @O_iErrorState <> 0
BEGIN
SET @O_iErrorState = 90
RETURN
END
EXEC @iStatus = Smformatstringsforexecs @I_vInputString = @I_charEndCustomer
,@O_cOutputString = @cEndCustomer OUTPUT
,@O_iErrorState = @O_iErrorState OUTPUT
IF @iStatus <> 0
OR @O_iErrorState <> 0
BEGIN
SET @O_iErrorState = 90
RETURN
END
SELECT @O_iErrorState = 0
IF @@trancount = 0
BEGIN
SELECT @tTransaction = 1
BEGIN TRANSACTION
END
WHILE (@tLoop IS NULL)
BEGIN
SELECT @tLoop = 1
/* Execute the PRE */
EXEC @iStatus = Rmcustomermodifierpre @I_charStartCustomer OUTPUT
,@I_charEndCustomer OUTPUT
,@cStartCustomer OUTPUT
,@cEndCustomer OUTPUT
,@O_iErrorState OUTPUT
IF @O_iErrorState <> 0
BEGIN
BREAK
END
IF @I_charStartCustomer IS NULL
OR @I_charEndCustomer IS NULL
OR @cStartCustomer IS NULL
OR @cEndCustomer IS NULL
BEGIN
SELECT @O_iErrorState = 1
BREAK
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE NAME = 'RM_Customer_MSTR_NA'
)
ALTER TABLE dbo.rm00101
DROP CONSTRAINT rm_customer_mstr_na
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 2
BREAK
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE NAME = 'RM_NationalAccounts_MSTR_FKC'
)
ALTER TABLE dbo.rm00105
DROP CONSTRAINT rm_nationalaccounts_mstr_fkc
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 3
BREAK
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.taCustomerInsert')
)
ALTER TABLE rm00101 disable TRIGGER tacustomerinsert
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 4
BREAK
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.taCustomerAddressInsert')
)
ALTER TABLE rm00102 disable TRIGGER tacustomeraddressinsert
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 5
BREAK
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.tr_SVC_Cust_Ext_U')
)
ALTER TABLE rm00102 disable TRIGGER tr_svc_cust_ext_u
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 40
BREAK
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.tr_SVC_RM00102_D')
)
ALTER TABLE rm00102 disable TRIGGER tr_svc_rm00102_d
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 41
BREAK
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.tr_SVC_SVC00601_IUD')
)
ALTER TABLE svc00601 disable TRIGGER tr_svc_svc00601_iud
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 42
BREAK
END
SELECT @BALNCTYP = balnctyp
FROM rm00101
WHERE custnmbr = @I_charStartCustomer
IF @BALNCTYP = 1
BEGIN
IF Len(@I_charEndCustomer) > Len(@I_charStartCustomer)
SELECT @LEN1 = (Len(@I_charEndCustomer) - Len(@I_charStartCustomer))
ELSE IF Len(@I_charEndCustomer) < Len(@I_charStartCustomer)
SELECT @LEN2 = (Len(@I_charStartCustomer) - Len(@I_charEndCustomer))
UPDATE rm20101
SET custnmbr = @I_charEndCustomer
,docnumbr = Rtrim(@I_charEndCustomer) + '^' + Replicate('0', @LEN2) + Substring(Rtrim(docnumbr), Len(@I_charStartCustomer) + 2 + @LEN1, 20)
WHERE custnmbr = @I_charStartCustomer
AND rmdtypal = 0
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 31
BREAK
END
UPDATE rm00401
SET custnmbr = @I_charEndCustomer
,docnumbr = Rtrim(@I_charEndCustomer) + '^' + Replicate('0', @LEN2) + Substring(Rtrim(docnumbr), Len(@I_charStartCustomer) + 2 + @LEN1, 20)
WHERE custnmbr = @I_charStartCustomer
AND rmdtypal = 0
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 32
BREAK
END
UPDATE rm30101
SET custnmbr = @I_charEndCustomer
,docnumbr = Rtrim(@I_charEndCustomer) + '^' + Replicate('0', @LEN2) + Substring(Rtrim(docnumbr), Len(@I_charStartCustomer) + 2 + @LEN1, 20)
,balfwdnm = Rtrim(@I_charEndCustomer) + '^' + Replicate('0', @LEN2) + Substring(Rtrim(balfwdnm), Len(@I_charStartCustomer) + 2 + @LEN1, 20)
WHERE custnmbr = @I_charStartCustomer
AND rmdtypal = 0
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 33
BREAK
END
UPDATE rm30101
SET custnmbr = @I_charEndCustomer
,balfwdnm = Rtrim(@I_charEndCustomer) + '^' + Replicate('0', @LEN2) + Substring(Rtrim(balfwdnm), Len(@I_charStartCustomer) + 2 + @LEN1, 20)
WHERE custnmbr = @I_charStartCustomer
AND rmdtypal <> 0
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 34
BREAK
END
END
INSERT INTO rm00101 (
custnmbr
,custname
,custclas
,cprcstnm
,cntcprsn
,stmtname
,shrtname
,adrscode
,upszone
,shipmthd
,taxschid
,address1
,address2
,address3
,country
,city
,STATE
,zip
,phone1
,phone2
,phone3
,fax
,prbtadcd
,prstadcd
,staddrcd
,slprsnid
,chekbkid
,pymtrmid
,crlmttyp
,crlmtamt
,crlmtper
,crlmtpam
,curncyid
,ratetpid
,custdisc
,prclevel
,minpytyp
,minpydlr
,minpypct
,fnchatyp
,fnchpcnt
,finchdlr
,mxwoftyp
,mxwrofam
,comment1
,comment2
,userdef1
,userdef2
,taxexmt1
,taxexmt2
,txrgnnum
,balnctyp
,stmtcycl
,bankname
,bnkbrnch
,salsterr
,defcacty
,rmcshacc
,rmaracc
,rmslsacc
,rmivacc
,rmcosacc
,rmtakacc
,rmavacc
,rmfcgacc
,rmwracc
,rmsoracc
,frstindt
,inactive
,hold
,crcardid
,crcrdnum
,ccrdxpdt
,kpdsthst
,kpcalhst
,kperhist
,kptrxhst
,noteindx
,creatddt
,modifdt
,revalue_customer
,post_results_to
,finchid
,govcrpid
,govindid
,disgrper
,duegrper
,docfmtid
,send_email_statements
,userlang
,gpsfointegrationid
,integrationsource
,integrationid
,orderfulfilldefault
,custpriority
,ccode
,declid
,rmovrpymtwrtoffacctidx
,shipcomplete
,cbvat
,includeindp
)
SELECT @I_charEndCustomer
,custname
,custclas
,cprcstnm
,cntcprsn
,stmtname
,shrtname
,adrscode
,upszone
,shipmthd
,taxschid
,address1
,address2
,address3
,country
,city
,STATE
,zip
,phone1
,phone2
,phone3
,fax
,prbtadcd
,prstadcd
,staddrcd
,slprsnid
,chekbkid
,pymtrmid
,crlmttyp
,crlmtamt
,crlmtper
,crlmtpam
,curncyid
,ratetpid
,custdisc
,prclevel
,minpytyp
,minpydlr
,minpypct
,fnchatyp
,fnchpcnt
,finchdlr
,mxwoftyp
,mxwrofam
,comment1
,comment2
,userdef1
,userdef2
,taxexmt1
,taxexmt2
,txrgnnum
,balnctyp
,stmtcycl
,bankname
,bnkbrnch
,salsterr
,defcacty
,rmcshacc
,rmaracc
,rmslsacc
,rmivacc
,rmcosacc
,rmtakacc
,rmavacc
,rmfcgacc
,rmwracc
,rmsoracc
,frstindt
,inactive
,hold
,crcardid
,crcrdnum
,ccrdxpdt
,kpdsthst
,kpcalhst
,kperhist
,kptrxhst
,noteindx
,creatddt
,modifdt
,revalue_customer
,post_results_to
,finchid
,govcrpid
,govindid
,disgrper
,duegrper
,docfmtid
,send_email_statements
,userlang
,gpsfointegrationid
,integrationsource
,integrationid
,orderfulfilldefault
,custpriority
,ccode
,declid
,rmovrpymtwrtoffacctidx
,shipcomplete
,cbvat
,includeindp
FROM rm00101
WHERE custnmbr = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 35
BREAK
END
DELETE rm00101
WHERE custnmbr = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 36
BREAK
END
INSERT INTO rm00102 (
custnmbr
,adrscode
,slprsnid
,upszone
,shipmthd
,taxschid
,cntcprsn
,address1
,address2
,address3
,country
,city
,STATE
,zip
,phone1
,phone2
,phone3
,fax
,modifdt
,creatddt
,gpsfointegrationid
,integrationsource
,integrationid
,ccode
,declid
,locncode
,salsterr
,userdef1
,userdef2
)
SELECT @I_charEndCustomer
,adrscode
,slprsnid
,upszone
,shipmthd
,taxschid
,cntcprsn
,address1
,address2
,address3
,country
,city
,STATE
,zip
,phone1
,phone2
,phone3
,fax
,modifdt
,creatddt
,gpsfointegrationid
,integrationsource
,integrationid
,ccode
,declid
,locncode
,salsterr
,userdef1
,userdef2
FROM rm00102
WHERE custnmbr = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 50
BREAK
END
DELETE rm00102
WHERE custnmbr = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 51
BREAK
END
DECLARE ta_cmcursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set CUSTNMBR =' + Rtrim(@cEndCustomer) + ' where CUSTNMBR = ' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'CUSTNMBR'
AND o.NAME <> 'SOP10100'
AND o.NAME <> 'SOP30200'
AND o.NAME <> 'RM00101'
AND o.NAME <> 'RM00102'
AND o.NAME <> 'DXPublish_Customer'
AND o.NAME <> 'DXPublish_Customer_Work'
AND o.NAME <> 'DXPublish_Address'
AND o.NAME <> 'DXPublish_Address_Work'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cmcursor
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 1
BREAK
END
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set STCUSTID =' + Rtrim(@cEndCustomer) + ' where STCUSTID = ' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'STCUSTID'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cmcursor
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 6
BREAK
END
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set ENCUSTID =' + Rtrim(@cEndCustomer) + ' where ENCUSTID = ' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'ENCUSTID'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cmcursor
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 7
BREAK
END
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set CPRCSTNM =' + Rtrim(@cEndCustomer) + ' where CPRCSTNM = ' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'CPRCSTNM'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cmcursor
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 8
BREAK
END
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set CustomerVendor_ID =' + Rtrim(@cEndCustomer) + ' where SERIES = 3 and CustomerVendor_ID = ' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'CustomerVendor_ID'
AND o.NAME <> 'TX30000'
AND o.NAME <> 'EDCVAT26'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cmcursor
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 9
BREAK
END
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set Bill_To_Customer =' + Rtrim(@cEndCustomer) + ' where Bill_To_Customer = ' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'Bill_To_Customer'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cmcursor
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 10
BREAK
END
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set customer_id =' + (@cEndCustomer) + ' where customer_id = ' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'customer_id'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cmcursor
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 11
BREAK
END
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set Bill_Customer_Number =' + (@cEndCustomer) + ' where Bill_Customer_Number = ' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'Bill_Customer_Number'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cmcursor
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 12
BREAK
END
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cmcursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set LINKCODE =' + (@cEndCustomer) + ' where PRODTCOD = ''C'' and LINKCODE = ' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
,syscolumns c2
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'LINKCODE'
AND c.id = c2.id
AND c2.NAME = 'PRODTCOD'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cmcursor
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 39
BREAK
END
FETCH NEXT
FROM ta_cmcursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cmcursor
IF @O_iErrorState <> 0
BREAK
DECLARE ta_cccursor INSENSITIVE CURSOR
FOR
SELECT 'update [' + o.NAME + '] set aaCustID=' + Rtrim(@cEndCustomer) + ' where aaCustID=' + Rtrim(@cStartCustomer)
FROM sysobjects o
,syscolumns c
WHERE o.id = c.id
AND o.type = 'U'
AND c.NAME = 'aaCustID'
ORDER BY o.NAME
SET NOCOUNT ON
OPEN ta_cccursor
FETCH NEXT
FROM ta_cccursor
INTO @CUSTNMBR
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
EXEC (@CUSTNMBR)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 88
BREAK
END
FETCH NEXT
FROM ta_cccursor
INTO @CUSTNMBR
END
DEALLOCATE ta_cccursor
IF @O_iErrorState <> 0
BREAK
UPDATE a
SET ORMSTRID = @I_charEndCustomer
FROM gl10001 a
,gl10000 b
WHERE a.ormstrid = @I_charStartCustomer
AND a.jrnentry = b.jrnentry
AND b.series = 3
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 13
BREAK
END
UPDATE gl20000
SET ormstrid = @I_charEndCustomer
WHERE ormstrid = @I_charStartCustomer
AND series = 3
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 14
BREAK
END
UPDATE gl30000
SET ormstrid = @I_charEndCustomer
WHERE ormstrid = @I_charStartCustomer
AND series = 3
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 15
BREAK
END
UPDATE pjournal
SET ormstrid = @I_charEndCustomer
WHERE ormstrid = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 16
BREAK
END
UPDATE cm20200
SET cmlinkid = @I_charEndCustomer
WHERE cmlinkid = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 17
BREAK
END
UPDATE cm20300
SET cmlinkid = @I_charEndCustomer
WHERE cmlinkid = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 18
BREAK
END
UPDATE tx30000
SET customervendor_id = @I_charEndCustomer
WHERE customervendor_id = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 218
BREAK
END
IF EXISTS (
SELECT 1
FROM sysobjects(NOLOCK)
WHERE NAME = 'EDCVAT26'
AND type = 'U'
)
BEGIN
UPDATE edcvat26
SET customervendor_id = @I_charEndCustomer
WHERE customervendor_id = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 219
BREAK
END
END
UPDATE mc10000
SET startcustorvendid = @I_charEndCustomer
WHERE startcustorvendid = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 19
BREAK
END
UPDATE mc10000
SET endcustorvendid = @I_charEndCustomer
WHERE endcustorvendid = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 20
BREAK
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.PP100100')
AND sysstat & 0xF = 3
)
BEGIN
UPDATE pp100100
SET custvndr = @I_charEndCustomer
WHERE custvndr = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 204
BREAK
END
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.PA50105')
AND sysstat & 0xF = 3
)
BEGIN
UPDATE pa50105
SET custvenid = @I_charEndCustomer
WHERE custvenid = @I_charStartCustomer
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 21
BREAK
END
END
UPDATE sop10100
SET custnmbr = @I_charEndCustomer
WHERE custnmbr = @I_charStartCustomer
AND prospect = 0
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 22
BREAK
END
UPDATE sop30200
SET custnmbr = @I_charEndCustomer
WHERE custnmbr = @I_charStartCustomer
AND prospect = 0
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 23
BREAK
END
UPDATE sy01200
SET master_id = @I_charEndCustomer
WHERE master_id = @I_charStartCustomer
AND master_type = 'CUS'
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 24
BREAK
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.PA01901')
AND sysstat & 0xF = 3
)
BEGIN
UPDATE pa01901
SET pacostowner = @I_charEndCustomer
WHERE pacostowner = @I_charStartCustomer
AND (
patrantype = 7
OR patrantype = 10
)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 25
BREAK
END
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.PA43001')
AND sysstat & 0xF = 3
)
BEGIN
UPDATE pa43001
SET parecordid = @I_charEndCustomer
WHERE parecordid = @I_charStartCustomer
AND (
pasfid = 21
OR pasfid = 36
)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 26
BREAK
END
END
IF @O_iErrorState <> 0
BREAK
ALTER TABLE dbo.rm00101
WITH NOCHECK ADD CONSTRAINT rm_customer_mstr_na CHECK (
(
cprcstnm <> ''
AND balnctyp = 0
)
OR (
cprcstnm = ''
AND balnctyp = 1
)
OR (
cprcstnm = ''
AND balnctyp = 0
)
)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 27
END
ALTER TABLE dbo.rm00105
WITH NOCHECK ADD CONSTRAINT rm_nationalaccounts_mstr_fkc FOREIGN KEY (cprcstnm) REFERENCES dbo.rm00101(custnmbr)
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 28
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.taCustomerInsert')
)
ALTER TABLE rm00101 enable TRIGGER tacustomerinsert
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 29
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.taCustomerAddressInsert')
)
ALTER TABLE rm00102 enable TRIGGER tacustomeraddressinsert
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 30
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.tr_SVC_Cust_Ext_U')
)
ALTER TABLE rm00102 enable TRIGGER tr_svc_cust_ext_u
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 38
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.tr_SVC_RM00102_D')
)
ALTER TABLE rm00102 enable TRIGGER tr_svc_rm00102_d
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 43
END
IF EXISTS (
SELECT *
FROM sysobjects
WHERE id = Object_id('dbo.tr_SVC_SVC00601_IUD')
)
ALTER TABLE svc00601 enable TRIGGER tr_svc_svc00601_iud
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 44
END
END
/* This is where we end up if an error occurs and BREAK is fired */
EXEC @iStatus = Rmcustomermodifierpost @I_charStartCustomer
,@I_charEndCustomer
,@cStartCustomer
,@cEndCustomer
,@O_iErrorState
IF @O_iErrorState <> 0
BEGIN
IF @tTransaction = 1
ROLLBACK TRANSACTION
END
ELSE IF @tTransaction = 1
BEGIN
COMMIT TRANSACTION
END
RETURN
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156