Stored Proc problem
in [Programming SQL]
|
From: David C on 18 Jul 2008 14:47 I have a new store procedure that is giving me 2 problems. First, it is returning a value of 1 instead of the actual new identity. Second, the TRAN is rolling back and I'm not sure why. Any help is appreciated. Below is the sp: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[fd_updMergerTrans] ( @CompanyID int, @LinkID int, @NewName nvarchar(100), @TransDate datetime, @NewLinkID int, @NewCompID int output ) AS DECLARE @CompanyIDNew int BEGIN TRAN T1 BEGIN TRY If @NewCompID <> 0 BEGIN INSERT INTO dbo.Company (LinkID, Company, Address1, Address2, City, [State], PostalCode, CountryID, MainPhone, MainFax, CompTypeID, LastUpdated, Notes, URL, Nature, CompanyGroup, LineID) SELECT @LinkID, @NewName, Address1, Address2, City, State, PostalCode, CountryID, MainPhone, MainFax, 1, CONVERT(CHAR(10),CURRENT_TIMESTAMP,101), Notes, URL, Nature, CompanyGroup, LineID FROM dbo.Company WHERE CompanyID = @CompanyID; SET @CompanyIDNew = IDENT_CURRENT('Company'); -- copy active contacts INSERT INTO Contacts ( OldID, LastName, FirstName, MainPhone, MainFax, Email, Notes, MiddleName, Salutation, Prefix, Title, ConAddr1, ConAddr2, ConCity, ConState, ConPostalCode, ConCountry ) SELECT tblLinkContact.ContactID, Contacts.LastName, Contacts.FirstName, Contacts.MainPhone, Contacts.MainFax, Contacts.Email, Contacts.Notes, Contacts.MiddleName, Contacts.Salutation, Contacts.Prefix, Contacts.Title, Contacts.ConAddr1, Contacts.ConAddr2, Contacts.ConCity, Contacts.ConState, Contacts.ConPostalCode, Contacts.ConCountry FROM Contacts INNER JOIN tblLinkContact ON Contacts.ContactID = tblLinkContact.ContactID WHERE (tblLinkContact.CompanyID = @CompanyID) AND (tblLinkContact.Active <> 0); -- create links to new contacts INSERT INTO tblLinkContact ( CompanyID, ContactID, Active ) SELECT @CompanyIDNew, Contacts.ContactID, 1 FROM Contacts INNER JOIN tblLinkContact ON Contacts.OldID = tblLinkContact.ContactID WHERE (tblLinkContact.CompanyID = @CompanyID); -- copy new locations to new company INSERT INTO tblLocation ( CompanyID, LinkID, LocAddress1, LocAddress2, LocCity, LocState, LocPostalCode, CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, ConstType, Sprinkler, Smoke, [Security], NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, LocActive ) SELECT @NewCompID, @NewLinkID, LocAddress1, LocAddress2, LocCity, LocState, LocPostalCode, CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, ConstType, Sprinkler, Smoke, [Security], NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, LocActive FROM tblLocation WHERE (CompanyID = @CompanyID); -- copy new locations related to legal company INSERT INTO tblLocation ( CompanyID, LinkID, LocAddress1, LocAddress2, LocCity, LocState, LocPostalCode, CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, ConstType, Sprinkler, Smoke, [Security], NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, LocActive ) SELECT CompanyID, @NewLinkID, LocAddress1, LocAddress2, LocCity, LocState, LocPostalCode, CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, ConstType, Sprinkler, Smoke, [Security], NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, LocActive FROM tblLocation WHERE (CompanyID <> @CompanyID) AND (LocActive <> 0) AND (LinkID = @LinkID); -- set inactive date as transaction date into merge from company UPDATE dbo.Company SET InactiveDate = @TransDate WHERE CompanyID = @CompanyID; -- inactivate all active contacts for from company UPDATE tblLinkContact SET Active = 0 WHERE (Active <> 0) AND (CompanyID = @CompanyID); -- inactivate officers and directors for from company UPDATE tblBoard SET EndTerm = @TransDate WHERE (EndTerm IS NULL) AND (LinkID = @LinkID); -- inactivate locations for from company UPDATE tblLocation SET LocActive = 0 WHERE (LocActive <> 0) AND (LinkID = @LinkID); SELECT @NewCompID = @CompanyIDNew; END -- update LinkID to new merged into company LinkID UPDATE Company SET LinkID = @NewLinkID WHERE (LinkID = @LinkID) AND (InactiveDate IS NULL); -- update ParentID to new LinkID on tblLinkCompany UPDATE tblLinkCompany SET ParentID = @NewLinkID FROM Company WHERE (tblLinkCompany.LinkID = Company.LinkID) AND (tblLinkCompany.ParentID = @LinkID) AND (Company.InactiveDate IS NULL); END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber; IF @@TRANCOUNT > 0 ROLLBACK TRAN T1; END CATCH; IF @@TRANCOUNT > 0 COMMIT TRAN T1; SELECT 0 As ErrorNumber; -David
From: Alex Kuznetsov on 18 Jul 2008 16:04 On Jul 18, 1:47 pm, "David C" <dlch...(a)lifetimeinc.com> wrote: > I have a new store procedure that is giving me 2 problems. > First, it is returning a value of 1 instead of the actual new identity. > Second, the TRAN is rolling back and I'm not sure why. Any help is > appreciated. > Below is the sp: > > SET ANSI_NULLS ON > GO > SET QUOTED_IDENTIFIER ON > GO > > ALTER PROCEDURE [dbo].[fd_updMergerTrans] > ( > @CompanyID int, > @LinkID int, > @NewName nvarchar(100), > @TransDate datetime, > @NewLinkID int, > @NewCompID int output > ) > AS > DECLARE @CompanyIDNew int > > BEGIN TRAN T1 > BEGIN TRY > If @NewCompID <> 0 > BEGIN > > INSERT INTO dbo.Company > (LinkID, Company, Address1, Address2, City, [State], PostalCode, > CountryID, > MainPhone, MainFax, CompTypeID, LastUpdated, Notes, URL, Nature, > CompanyGroup, LineID) > SELECT @LinkID, @NewName, Address1, Address2, City, State, PostalCode, > CountryID, > MainPhone, MainFax, 1, CONVERT(CHAR(10),CURRENT_TIMESTAMP,101), > Notes, URL, Nature, CompanyGroup, LineID > FROM dbo.Company > WHERE CompanyID = @CompanyID; > > SET @CompanyIDNew = IDENT_CURRENT('Company'); > > -- copy active contacts > INSERT INTO Contacts > ( OldID, LastName, FirstName, MainPhone, MainFax, > Email, Notes, MiddleName, Salutation, > Prefix, Title, ConAddr1, ConAddr2, ConCity, ConState, > ConPostalCode, ConCountry ) > SELECT tblLinkContact.ContactID, Contacts.LastName, Contacts.FirstName, > Contacts.MainPhone, Contacts.MainFax, > Contacts.Email, Contacts.Notes, Contacts.MiddleName, > Contacts.Salutation, > Contacts.Prefix, Contacts.Title, Contacts.ConAddr1, Contacts.ConAddr2, > Contacts.ConCity, Contacts.ConState, > Contacts.ConPostalCode, Contacts.ConCountry > FROM Contacts INNER JOIN tblLinkContact ON Contacts.ContactID = > tblLinkContact.ContactID > WHERE (tblLinkContact.CompanyID = @CompanyID) > AND (tblLinkContact.Active <> 0); > > -- create links to new contacts > INSERT INTO tblLinkContact ( CompanyID, ContactID, Active ) > SELECT @CompanyIDNew, Contacts.ContactID, 1 > FROM Contacts INNER JOIN tblLinkContact ON Contacts.OldID = > tblLinkContact.ContactID > WHERE (tblLinkContact.CompanyID = @CompanyID); > > -- copy new locations to new company > INSERT INTO tblLocation ( CompanyID, LinkID, LocAddress1, LocAddress2, > LocCity, LocState, LocPostalCode, > CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, > ConstType, Sprinkler, Smoke, [Security], > NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, > LocActive ) > SELECT @NewCompID, @NewLinkID, LocAddress1, LocAddress2, LocCity, > LocState, LocPostalCode, > CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, > ConstType, Sprinkler, Smoke, [Security], > NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, > LocActive > FROM tblLocation > WHERE (CompanyID = @CompanyID); > > -- copy new locations related to legal company > INSERT INTO tblLocation ( CompanyID, LinkID, LocAddress1, LocAddress2, > LocCity, LocState, LocPostalCode, > CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, > ConstType, Sprinkler, Smoke, [Security], > NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, > LocActive ) > SELECT CompanyID, @NewLinkID, LocAddress1, LocAddress2, LocCity, > LocState, LocPostalCode, > CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, > ConstType, Sprinkler, Smoke, [Security], > NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, > LocActive > FROM tblLocation > WHERE (CompanyID <> @CompanyID) > AND (LocActive <> 0) > AND (LinkID = @LinkID); > > -- set inactive date as transaction date into merge from company > UPDATE dbo.Company > SET InactiveDate = @TransDate > WHERE CompanyID = @CompanyID; > > -- inactivate all active contacts for from company > UPDATE tblLinkContact > SET Active = 0 > WHERE (Active <> 0) > AND (CompanyID = @CompanyID); > > -- inactivate officers and directors for from company > UPDATE tblBoard > SET EndTerm = @TransDate > WHERE (EndTerm IS NULL) > AND (LinkID = @LinkID); > > -- inactivate locations for from company > UPDATE tblLocation > SET LocActive = 0 > WHERE (LocActive <> 0) > AND (LinkID = @LinkID); > > SELECT @NewCompID = @CompanyIDNew; > > END > > -- update LinkID to new merged into company LinkID > UPDATE Company > SET LinkID = @NewLinkID > WHERE (LinkID = @LinkID) > AND (InactiveDate IS NULL); > > -- update ParentID to new LinkID on tblLinkCompany > UPDATE tblLinkCompany > SET ParentID = @NewLinkID > FROM Company > WHERE (tblLinkCompany.LinkID = Company.LinkID) > AND (tblLinkCompany.ParentID = @LinkID) > AND (Company.InactiveDate IS NULL); > > END TRY > BEGIN CATCH > SELECT ERROR_NUMBER() AS ErrorNumber; > > IF @@TRANCOUNT > 0 > ROLLBACK TRAN T1; > END CATCH; > > IF @@TRANCOUNT > 0 > COMMIT TRAN T1; > SELECT 0 As ErrorNumber; > > -David instead of IDENT_CURRENT('Company'); you can be more specific: IDENT_CURRENT('yourSchema.Company'); or even better use SCOPE_IDENTITY. Also use SELECT ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_LINE()
From: David C on 18 Jul 2008 16:29 Wouldn't SCOPE_IDENTITY() return wrong value since I have more INSERTs after the first one? David "Alex Kuznetsov" <alkuzo(a)gmail.com> wrote in message news:62ed19db-f1c0-43c9-a19e-d2d8de06e11a(a)p31g2000prf.googlegroups.com... > On Jul 18, 1:47 pm, "David C" <dlch...(a)lifetimeinc.com> wrote: >> I have a new store procedure that is giving me 2 problems. >> First, it is returning a value of 1 instead of the actual new identity. >> Second, the TRAN is rolling back and I'm not sure why. Any help is >> appreciated. >> Below is the sp: >> >> SET ANSI_NULLS ON >> GO >> SET QUOTED_IDENTIFIER ON >> GO >> >> ALTER PROCEDURE [dbo].[fd_updMergerTrans] >> ( >> @CompanyID int, >> @LinkID int, >> @NewName nvarchar(100), >> @TransDate datetime, >> @NewLinkID int, >> @NewCompID int output >> ) >> AS >> DECLARE @CompanyIDNew int >> >> BEGIN TRAN T1 >> BEGIN TRY >> If @NewCompID <> 0 >> BEGIN >> >> INSERT INTO dbo.Company >> (LinkID, Company, Address1, Address2, City, [State], PostalCode, >> CountryID, >> MainPhone, MainFax, CompTypeID, LastUpdated, Notes, URL, Nature, >> CompanyGroup, LineID) >> SELECT @LinkID, @NewName, Address1, Address2, City, State, >> PostalCode, >> CountryID, >> MainPhone, MainFax, 1, CONVERT(CHAR(10),CURRENT_TIMESTAMP,101), >> Notes, URL, Nature, CompanyGroup, LineID >> FROM dbo.Company >> WHERE CompanyID = @CompanyID; >> >> SET @CompanyIDNew = IDENT_CURRENT('Company'); >> >> -- copy active contacts >> INSERT INTO Contacts >> ( OldID, LastName, FirstName, MainPhone, MainFax, >> Email, Notes, MiddleName, Salutation, >> Prefix, Title, ConAddr1, ConAddr2, ConCity, ConState, >> ConPostalCode, ConCountry ) >> SELECT tblLinkContact.ContactID, Contacts.LastName, >> Contacts.FirstName, >> Contacts.MainPhone, Contacts.MainFax, >> Contacts.Email, Contacts.Notes, Contacts.MiddleName, >> Contacts.Salutation, >> Contacts.Prefix, Contacts.Title, Contacts.ConAddr1, >> Contacts.ConAddr2, >> Contacts.ConCity, Contacts.ConState, >> Contacts.ConPostalCode, Contacts.ConCountry >> FROM Contacts INNER JOIN tblLinkContact ON Contacts.ContactID = >> tblLinkContact.ContactID >> WHERE (tblLinkContact.CompanyID = @CompanyID) >> AND (tblLinkContact.Active <> 0); >> >> -- create links to new contacts >> INSERT INTO tblLinkContact ( CompanyID, ContactID, Active ) >> SELECT @CompanyIDNew, Contacts.ContactID, 1 >> FROM Contacts INNER JOIN tblLinkContact ON Contacts.OldID = >> tblLinkContact.ContactID >> WHERE (tblLinkContact.CompanyID = @CompanyID); >> >> -- copy new locations to new company >> INSERT INTO tblLocation ( CompanyID, LinkID, LocAddress1, >> LocAddress2, >> LocCity, LocState, LocPostalCode, >> CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, >> ConstType, Sprinkler, Smoke, [Security], >> NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, >> LocActive ) >> SELECT @NewCompID, @NewLinkID, LocAddress1, LocAddress2, LocCity, >> LocState, LocPostalCode, >> CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, >> ConstType, Sprinkler, Smoke, [Security], >> NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, >> LocActive >> FROM tblLocation >> WHERE (CompanyID = @CompanyID); >> >> -- copy new locations related to legal company >> INSERT INTO tblLocation ( CompanyID, LinkID, LocAddress1, >> LocAddress2, >> LocCity, LocState, LocPostalCode, >> CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, >> ConstType, Sprinkler, Smoke, [Security], >> NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, >> LocActive ) >> SELECT CompanyID, @NewLinkID, LocAddress1, LocAddress2, LocCity, >> LocState, LocPostalCode, >> CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, >> ConstType, Sprinkler, Smoke, [Security], >> NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, >> LocActive >> FROM tblLocation >> WHERE (CompanyID <> @CompanyID) >> AND (LocActive <> 0) >> AND (LinkID = @LinkID); >> >> -- set inactive date as transaction date into merge from company >> UPDATE dbo.Company >> SET InactiveDate = @TransDate >> WHERE CompanyID = @CompanyID; >> >> -- inactivate all active contacts for from company >> UPDATE tblLinkContact >> SET Active = 0 >> WHERE (Active <> 0) >> AND (CompanyID = @CompanyID); >> >> -- inactivate officers and directors for from company >> UPDATE tblBoard >> SET EndTerm = @TransDate >> WHERE (EndTerm IS NULL) >> AND (LinkID = @LinkID); >> >> -- inactivate locations for from company >> UPDATE tblLocation >> SET LocActive = 0 >> WHERE (LocActive <> 0) >> AND (LinkID = @LinkID); >> >> SELECT @NewCompID = @CompanyIDNew; >> >> END >> >> -- update LinkID to new merged into company LinkID >> UPDATE Company >> SET LinkID = @NewLinkID >> WHERE (LinkID = @LinkID) >> AND (InactiveDate IS NULL); >> >> -- update ParentID to new LinkID on tblLinkCompany >> UPDATE tblLinkCompany >> SET ParentID = @NewLinkID >> FROM Company >> WHERE (tblLinkCompany.LinkID = Company.LinkID) >> AND (tblLinkCompany.ParentID = @LinkID) >> AND (Company.InactiveDate IS NULL); >> >> END TRY >> BEGIN CATCH >> SELECT ERROR_NUMBER() AS ErrorNumber; >> >> IF @@TRANCOUNT > 0 >> ROLLBACK TRAN T1; >> END CATCH; >> >> IF @@TRANCOUNT > 0 >> COMMIT TRAN T1; >> SELECT 0 As ErrorNumber; >> >> -David > > instead of > > IDENT_CURRENT('Company'); > > you can be more specific: > > IDENT_CURRENT('yourSchema.Company'); > > or even better use SCOPE_IDENTITY. Also use > > SELECT ERROR_NUMBER(), > ERROR_MESSAGE(), > ERROR_PROCEDURE(), > ERROR_LINE()
From: David C on 18 Jul 2008 16:42 Also, it still does not work. It is always returning a zero and not creating a company record. David "Alex Kuznetsov" <alkuzo(a)gmail.com> wrote in message news:62ed19db-f1c0-43c9-a19e-d2d8de06e11a(a)p31g2000prf.googlegroups.com... > On Jul 18, 1:47 pm, "David C" <dlch...(a)lifetimeinc.com> wrote: >> I have a new store procedure that is giving me 2 problems. >> First, it is returning a value of 1 instead of the actual new identity. >> Second, the TRAN is rolling back and I'm not sure why. Any help is >> appreciated. >> Below is the sp: >> >> SET ANSI_NULLS ON >> GO >> SET QUOTED_IDENTIFIER ON >> GO >> >> ALTER PROCEDURE [dbo].[fd_updMergerTrans] >> ( >> @CompanyID int, >> @LinkID int, >> @NewName nvarchar(100), >> @TransDate datetime, >> @NewLinkID int, >> @NewCompID int output >> ) >> AS >> DECLARE @CompanyIDNew int >> >> BEGIN TRAN T1 >> BEGIN TRY >> If @NewCompID <> 0 >> BEGIN >> >> INSERT INTO dbo.Company >> (LinkID, Company, Address1, Address2, City, [State], PostalCode, >> CountryID, >> MainPhone, MainFax, CompTypeID, LastUpdated, Notes, URL, Nature, >> CompanyGroup, LineID) >> SELECT @LinkID, @NewName, Address1, Address2, City, State, >> PostalCode, >> CountryID, >> MainPhone, MainFax, 1, CONVERT(CHAR(10),CURRENT_TIMESTAMP,101), >> Notes, URL, Nature, CompanyGroup, LineID >> FROM dbo.Company >> WHERE CompanyID = @CompanyID; >> >> SET @CompanyIDNew = IDENT_CURRENT('Company'); >> >> -- copy active contacts >> INSERT INTO Contacts >> ( OldID, LastName, FirstName, MainPhone, MainFax, >> Email, Notes, MiddleName, Salutation, >> Prefix, Title, ConAddr1, ConAddr2, ConCity, ConState, >> ConPostalCode, ConCountry ) >> SELECT tblLinkContact.ContactID, Contacts.LastName, >> Contacts.FirstName, >> Contacts.MainPhone, Contacts.MainFax, >> Contacts.Email, Contacts.Notes, Contacts.MiddleName, >> Contacts.Salutation, >> Contacts.Prefix, Contacts.Title, Contacts.ConAddr1, >> Contacts.ConAddr2, >> Contacts.ConCity, Contacts.ConState, >> Contacts.ConPostalCode, Contacts.ConCountry >> FROM Contacts INNER JOIN tblLinkContact ON Contacts.ContactID = >> tblLinkContact.ContactID >> WHERE (tblLinkContact.CompanyID = @CompanyID) >> AND (tblLinkContact.Active <> 0); >> >> -- create links to new contacts >> INSERT INTO tblLinkContact ( CompanyID, ContactID, Active ) >> SELECT @CompanyIDNew, Contacts.ContactID, 1 >> FROM Contacts INNER JOIN tblLinkContact ON Contacts.OldID = >> tblLinkContact.ContactID >> WHERE (tblLinkContact.CompanyID = @CompanyID); >> >> -- copy new locations to new company >> INSERT INTO tblLocation ( CompanyID, LinkID, LocAddress1, >> LocAddress2, >> LocCity, LocState, LocPostalCode, >> CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, >> ConstType, Sprinkler, Smoke, [Security], >> NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, >> LocActive ) >> SELECT @NewCompID, @NewLinkID, LocAddress1, LocAddress2, LocCity, >> LocState, LocPostalCode, >> CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, >> ConstType, Sprinkler, Smoke, [Security], >> NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, >> LocActive >> FROM tblLocation >> WHERE (CompanyID = @CompanyID); >> >> -- copy new locations related to legal company >> INSERT INTO tblLocation ( CompanyID, LinkID, LocAddress1, >> LocAddress2, >> LocCity, LocState, LocPostalCode, >> CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, >> ConstType, Sprinkler, Smoke, [Security], >> NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, >> LocActive ) >> SELECT CompanyID, @NewLinkID, LocAddress1, LocAddress2, LocCity, >> LocState, LocPostalCode, >> CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, >> ConstType, Sprinkler, Smoke, [Security], >> NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, >> LocActive >> FROM tblLocation >> WHERE (CompanyID <> @CompanyID) >> AND (LocActive <> 0) >> AND (LinkID = @LinkID); >> >> -- set inactive date as transaction date into merge from company >> UPDATE dbo.Company >> SET InactiveDate = @TransDate >> WHERE CompanyID = @CompanyID; >> >> -- inactivate all active contacts for from company >> UPDATE tblLinkContact >> SET Active = 0 >> WHERE (Active <> 0) >> AND (CompanyID = @CompanyID); >> >> -- inactivate officers and directors for from company >> UPDATE tblBoard >> SET EndTerm = @TransDate >> WHERE (EndTerm IS NULL) >> AND (LinkID = @LinkID); >> >> -- inactivate locations for from company >> UPDATE tblLocation >> SET LocActive = 0 >> WHERE (LocActive <> 0) >> AND (LinkID = @LinkID); >> >> SELECT @NewCompID = @CompanyIDNew; >> >> END >> >> -- update LinkID to new merged into company LinkID >> UPDATE Company >> SET LinkID = @NewLinkID >> WHERE (LinkID = @LinkID) >> AND (InactiveDate IS NULL); >> >> -- update ParentID to new LinkID on tblLinkCompany >> UPDATE tblLinkCompany >> SET ParentID = @NewLinkID >> FROM Company >> WHERE (tblLinkCompany.LinkID = Company.LinkID) >> AND (tblLinkCompany.ParentID = @LinkID) >> AND (Company.InactiveDate IS NULL); >> >> END TRY >> BEGIN CATCH >> SELECT ERROR_NUMBER() AS ErrorNumber; >> >> IF @@TRANCOUNT > 0 >> ROLLBACK TRAN T1; >> END CATCH; >> >> IF @@TRANCOUNT > 0 >> COMMIT TRAN T1; >> SELECT 0 As ErrorNumber; >> >> -David > > instead of > > IDENT_CURRENT('Company'); > > you can be more specific: > > IDENT_CURRENT('yourSchema.Company'); > > or even better use SCOPE_IDENTITY. Also use > > SELECT ERROR_NUMBER(), > ERROR_MESSAGE(), > ERROR_PROCEDURE(), > ERROR_LINE()
From: Alex Kuznetsov on 18 Jul 2008 16:43
On Jul 18, 3:29 pm, "David C" <dlch...(a)lifetimeinc.com> wrote: > Wouldn't SCOPE_IDENTITY() return wrong value since I have more INSERTs after > the first one? > > David"Alex Kuznetsov" <alk...(a)gmail.com> wrote in message > > news:62ed19db-f1c0-43c9-a19e-d2d8de06e11a(a)p31g2000prf.googlegroups.com... > > > On Jul 18, 1:47 pm, "David C" <dlch...(a)lifetimeinc.com> wrote: > >> I have a new store procedure that is giving me 2 problems. > >> First, it is returning a value of 1 instead of the actual new identity. > >> Second, the TRAN is rolling back and I'm not sure why. Any help is > >> appreciated. > >> Below is the sp: > > >> SET ANSI_NULLS ON > >> GO > >> SET QUOTED_IDENTIFIER ON > >> GO > > >> ALTER PROCEDURE [dbo].[fd_updMergerTrans] > >> ( > >> @CompanyID int, > >> @LinkID int, > >> @NewName nvarchar(100), > >> @TransDate datetime, > >> @NewLinkID int, > >> @NewCompID int output > >> ) > >> AS > >> DECLARE @CompanyIDNew int > > >> BEGIN TRAN T1 > >> BEGIN TRY > >> If @NewCompID <> 0 > >> BEGIN > > >> INSERT INTO dbo.Company > >> (LinkID, Company, Address1, Address2, City, [State], PostalCode, > >> CountryID, > >> MainPhone, MainFax, CompTypeID, LastUpdated, Notes, URL, Nature, > >> CompanyGroup, LineID) > >> SELECT @LinkID, @NewName, Address1, Address2, City, State, > >> PostalCode, > >> CountryID, > >> MainPhone, MainFax, 1, CONVERT(CHAR(10),CURRENT_TIMESTAMP,101), > >> Notes, URL, Nature, CompanyGroup, LineID > >> FROM dbo.Company > >> WHERE CompanyID = @CompanyID; > > >> SET @CompanyIDNew = IDENT_CURRENT('Company'); > > >> -- copy active contacts > >> INSERT INTO Contacts > >> ( OldID, LastName, FirstName, MainPhone, MainFax, > >> Email, Notes, MiddleName, Salutation, > >> Prefix, Title, ConAddr1, ConAddr2, ConCity, ConState, > >> ConPostalCode, ConCountry ) > >> SELECT tblLinkContact.ContactID, Contacts.LastName, > >> Contacts.FirstName, > >> Contacts.MainPhone, Contacts.MainFax, > >> Contacts.Email, Contacts.Notes, Contacts.MiddleName, > >> Contacts.Salutation, > >> Contacts.Prefix, Contacts.Title, Contacts.ConAddr1, > >> Contacts.ConAddr2, > >> Contacts.ConCity, Contacts.ConState, > >> Contacts.ConPostalCode, Contacts.ConCountry > >> FROM Contacts INNER JOIN tblLinkContact ON Contacts.ContactID = > >> tblLinkContact.ContactID > >> WHERE (tblLinkContact.CompanyID = @CompanyID) > >> AND (tblLinkContact.Active <> 0); > > >> -- create links to new contacts > >> INSERT INTO tblLinkContact ( CompanyID, ContactID, Active ) > >> SELECT @CompanyIDNew, Contacts.ContactID, 1 > >> FROM Contacts INNER JOIN tblLinkContact ON Contacts.OldID = > >> tblLinkContact.ContactID > >> WHERE (tblLinkContact.CompanyID = @CompanyID); > > >> -- copy new locations to new company > >> INSERT INTO tblLocation ( CompanyID, LinkID, LocAddress1, > >> LocAddress2, > >> LocCity, LocState, LocPostalCode, > >> CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, > >> ConstType, Sprinkler, Smoke, [Security], > >> NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, > >> LocActive ) > >> SELECT @NewCompID, @NewLinkID, LocAddress1, LocAddress2, LocCity, > >> LocState, LocPostalCode, > >> CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, > >> ConstType, Sprinkler, Smoke, [Security], > >> NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, > >> LocActive > >> FROM tblLocation > >> WHERE (CompanyID = @CompanyID); > > >> -- copy new locations related to legal company > >> INSERT INTO tblLocation ( CompanyID, LinkID, LocAddress1, > >> LocAddress2, > >> LocCity, LocState, LocPostalCode, > >> CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, > >> ConstType, Sprinkler, Smoke, [Security], > >> NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, > >> LocActive ) > >> SELECT CompanyID, @NewLinkID, LocAddress1, LocAddress2, LocCity, > >> LocState, LocPostalCode, > >> CountryID, LocCountry, [Space], [Property], Zoning, ConstDate, > >> ConstType, Sprinkler, Smoke, [Security], > >> NoEmp, LocNotes, Ownership, LeaseDate, LeaseFile, InsReq, InsValues, > >> LocActive > >> FROM tblLocation > >> WHERE (CompanyID <> @CompanyID) > >> AND (LocActive <> 0) > >> AND (LinkID = @LinkID); > > >> -- set inactive date as transaction date into merge from company > >> UPDATE dbo.Company > >> SET InactiveDate = @TransDate > >> WHERE CompanyID = @CompanyID; > > >> -- inactivate all active contacts for from company > >> UPDATE tblLinkContact > >> SET Active = 0 > >> WHERE (Active <> 0) > >> AND (CompanyID = @CompanyID); > > >> -- inactivate officers and directors for from company > >> UPDATE tblBoard > >> SET EndTerm = @TransDate > >> WHERE (EndTerm IS NULL) > >> AND (LinkID = @LinkID); > > >> -- inactivate locations for from company > >> UPDATE tblLocation > >> SET LocActive = 0 > >> WHERE (LocActive <> 0) > >> AND (LinkID = @LinkID); > > >> SELECT @NewCompID = @CompanyIDNew; > > >> END > > >> -- update LinkID to new merged into company LinkID > >> UPDATE Company > >> SET LinkID = @NewLinkID > >> WHERE (LinkID = @LinkID) > >> AND (InactiveDate IS NULL); > > >> -- update ParentID to new LinkID on tblLinkCompany > >> UPDATE tblLinkCompany > >> SET ParentID = @NewLinkID > >> FROM Company > >> WHERE (tblLinkCompany.LinkID = Company.LinkID) > >> AND (tblLinkCompany.ParentID = @LinkID) > >> AND (Company.InactiveDate IS NULL); > > >> END TRY > >> BEGIN CATCH > >> SELECT ERROR_NUMBER() AS ErrorNumber; > > >> IF @@TRANCOUNT > 0 > >> ROLLBACK TRAN T1; > >> END CATCH; > > >> IF @@TRANCOUNT > 0 > >> COMMIT TRAN T1; > >> SELECT 0 As ErrorNumber; > > >> -David > > > instead of > > > IDENT_CURRENT('Company'); > > > you can be more specific: > > > IDENT_CURRENT('yourSchema.Company'); > > > or even better use SCOPE_IDENTITY. Also use > > > SELECT ERROR_NUMBER(), > > ERROR_MESSAGE(), > > ERROR_PROCEDURE(), > > ERROR_LINE() If you save it in a local variable, no, the local variable won't change. |