SQL Stored Procedure - Import Address
WARNING: This is not kids stuff. Use of this feature requires expertise in SQL Server and in the Cyrious database structure. It is very possible to irreversibly damage your Cyrious Control database if you make a mistake. Always develop your tests using a separate database on a separate machine. If you are interested in using sql_bridge but are not a SQL guru, please contact a sales or consulting at Cyrious.
Note: This feature requires Control 5.75 or higher.
Overview
The stored procedure can be used to add an address (with Address Links if desired) in Control.
The following values can be supplied:
- @
If the following values are supplied, links for these records will be created automatically for this address
- @
You can use these output parameters to retrieve the IDs created:
- @AddressID - The Address.ID of the new record
- @AddressLinkID - The _first_ AddressLink.ID created. Subsequent links use incremented numbers.
The Stored Procedure returns
- AddressID,
- AddressClassTypeID
- AddressLinkID
- AddressLinkClassTypeID
Notes:
- In general, don't set values you want to use the default to. For instance, if you set the @TaxClassID to the value the customer is set to, this will force it to be overridden and it won't change even if the customer's tax class is updated. Just leave these columns out of the call if you want to use the default value.
The stored procedure requires sql_bridge in order to obtain IDs for the inserted records. sql_bridge is a collection of SQL Server stored procedures and functions and external modules that can be called to safely insert or update data into the Control database and notify the CHAPI service that data has been updated. The CHAPI service is a controller program that will then notify the SSLIP and all copies of Control about the changes.
Example Usage
This creates an address and links it to the shipment and to contact record:
-- Figure out the ID of the order DECLARE @THID INT = (SELECT ID FROM TransHeader WHERE OrderNumber = 1975 AND TransactionType = 1); EXEC dbo.csp_ImportAddress @StreetAddress1 = '9432 Common St' , @StreetAddress2 = 'Suite C' , @City = 'Baton Rouge' , @State = 'LA' , @PostalCode = '70809' , @AddressName = 'Corporate Office' , @LinkTHID = @THID , @LinkTHType1ID = 10 -- Shipping ;
This creates an address and links it to the Company record:
-- [Optional] Create variables to capture the results DECLARE @AddressID INT = NULL; DECLARE @AddressLinkID INT = NULL; -- Figure out what company DECLARE @AccountID INT = (SELECT ID FROM Account WHERE CompanyName LIKE 'ABC %'); EXEC dbo.csp_ImportAddress @StreetAddress1 = '9432 Common St' , @StreetAddress2 = 'Suite C' , @City = 'Baton Rouge' , @State = 'LA' , @PostalCode = '70809' , @LinkAccountID = @AccountID , @LinkAccountType1ID = 10 -- Billing , @LinkAccountType2ID = 11 -- Shipping -- Some OUTPUT Parameters in case the caller wants any of these value back , @AddressID = @AddressID OUTPUT , @AddressLinkID = @AddressLinkID OUTPUT ;
Stored Procedure
The SQL to create the order import stored procedure follows. This must be run to create the stored procedure before it can be used.
-- ============================================= -- Author: Cyrious Sofware -- Create date: May-2016 -- Description: This stored procedure adds an address to an order, contact, or company -- -- Returns: New AddressID, AddressClassTypeID -- ============================================= ALTER PROCEDURE csp_ImportAddress -- Input Parameter Values @StreetAddress1 VARCHAR(60) = NULL, @StreetAddress2 VARCHAR(60) = NULL, @City VARCHAR(25) = NULL, @State VARCHAR(25) = NULL, @PostalCode VARCHAR(25) = NULL, @County VARCHAR(25) = NULL, @Country VARCHAR(25) = 'US', @IsOneTimeAddress BIT = 0, @AddressName VARCHAR(25) = NULL, @RefreshOnSave BIT = 1, -- Set to 0 to NOT trigger a refresh in the link parent () -- if you must supply the following ID values, AddressLinks will be created for them -- To create Links to a TransHeader @LinkTHID INT = NULL, -- the ID of the order record @LinkTHType1ID TINYINT = NULL, -- Set to 10 for billing, 11 for shipping, NULL for temporary @LinkTHType2ID TINYINT = NULL, -- Set to NULL to only create 1 link -- To create Links to a Customer @LinkAccountID INT = NULL, -- the ID of the order record @LinkAccountType1ID TINYINT = NULL, -- Set to 10 for billing, 11 for shipping, NULL for temporary @LinkAccountType2ID TINYINT = NULL, -- Set to NULL to only create 1 link -- To create Links to a Contact @LinkContactID INT = NULL, -- the ID of the order record @LinkContactType1ID TINYINT = NULL, -- Set to 10 for billing, 11 for shipping, NULL for temporary @LinkContactType2ID TINYINT = NULL, -- Set to NULL to only create 1 link -- To create Links to a Shipment @LinkShipmentID INT = NULL, -- the ID of the order record @LinkShipmentType1ID TINYINT = NULL, -- Set to 10 for billing, 11 for shipping, NULL for temporary -- Some OUTPUT Parameters in case the caller wants any of these value back @AddressID INT = NULL OUTPUT, -- New Address ID. @AddressLinkID INT = NULL OUTPUT -- New AddressLink ID for the FIRST address link. AS BEGIN -- Define some error variables in case we need them DECLARE @DT SMALLDATETIME = GetDate(); DECLARE @ComputerName VARCHAR(25) = @@ServerName; DECLARE @NewLine CHAR(2) = CHAR(10)+CHAR(13); DECLARE @ProcName VARCHAR(50) = OBJECT_NAME(@@PROCID); -- Avoid duplicate postings IF (@LinkTHType1ID = @LinkTHType2ID) SET @LinkTHType2ID = NULL; IF (@LinkAccountType1ID = @LinkAccountType2ID) SET @LinkAccountType2ID = NULL; IF (@LinkContactType1ID = @LinkContactType2ID) SET @LinkContactType2ID = NULL; -- -- Step 1. Declare some variable and look up some information -- SET @AddressID = (SELECT dbo.csf_chapi_nextid( 4001, 1)); -- Address ID DECLARE @LinkCount INT; SET @LinkCount = ( IsNumeric(@LinkTHID) + IsNumeric(@LinkTHType2ID) + IsNumeric(@LinkAccountID) + IsNumeric(@LinkAccountType2ID) + IsNumeric(@LinkContactID) + IsNumeric(@LinkContactType2ID) + IsNumeric(@LinkShipmentID) ); DECLARE @IsMaster BIT = 1; -- Use this to set the first on to IsMaster ... IF (@LinkCount > 0) SET @AddressLinkID = (SELECT dbo.csf_chapi_nextid( 4002, @LinkCount)); -- Address Links -- -- Step 2. Create the Records -- BEGIN TRANSACTION BEGIN TRY -- Insert Address INSERT INTO [dbo].[Address] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [StreetAddress1], [StreetAddress2], [City], [State], [County], [PostalCode], [Country], [FormattedText], [TaxClassID], [IsValidated], [ValidatedAddress], [HasValidationError], [ValidationError]) VALUES ( @AddressID -- <ID, int,> , -1 -- ,<StoreID, int,> , 4001 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @StreetAddress1 -- <StreetAddress1, varchar(40),> , @StreetAddress2 -- <StreetAddress2, varchar(40),> , @City -- <City, varchar(25),> , @State -- <State, varchar(50),> , @County -- <County, varchar(50),> , @PostalCode -- <PostalCode, varchar(50),> , @Country -- <Country, varchar(50),> , @StreetAddress1 + @NewLine + (CASE WHEN len(COALESCE(@StreetAddress2, '')) > 1 THEN @StreetAddress2 + @NewLine ELSE '' END) + @City + ', ' + @State + ' ' + @PostalCode -- <FormattedText, varchar(max),> , NULL -- <TaxClassID, int,> , 0 -- <IsValidated, bit,> , NULL -- <ValidatedAddress, varchar(max),> , 0 -- <HasValidationError, bit,> , NULL -- <ValidationError, varchar(max),> ); -- INSERT Account Link ID if desired IF (@LinkAccountID IS NOT NULL) BEGIN SET @LinkCount = @LinkCount - 1; INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName], [IsOneTimeCompany]) VALUES ( @AddressLinkID + @LinkCount -- <ID, int,> , -1 -- ,<StoreID, int,> , 4002 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @IsMaster -- <IsMaster, bit,> , @LinkAccountID -- <ParentID, int,> , 2000 -- <ParentClassTypeID, smallint,> , @LinkAccountType1ID -- Billing Address -- <AddressTypeID, tinyint,> , @AddressID -- <AddressID, int,> , COALESCE(@AddressName, CASE @LinkAccountType1ID WHEN 10 THEN 'Billing' WHEN 11 THEN 'Shipping' ELSE 'Temporary' END) , @IsOneTimeAddress -- <IsOneTimeCompany, bit,> ); -- Delete the old Billing or Shipping Address Link if we are replacing it IF (@LinkAccountType1ID IN (10,11)) BEGIN DELETE FROM AddressLink WHERE ParentID = @LinkAccountID AND ParentClassTypeID = 2000 AND AddressTypeID = @LinkAccountType1ID AND AddressID != @AddressID; END; -- Update the Company if the Link is changing the Billing Address IF (@LinkAccountType1ID = 10) UPDATE Account SET SeqID = SeqID + 1, BillingAddressID = @AddressID WHERE ID = @LinkAccountID ; -- Update the Company if the Link is changing the Shipping Address IF (@LinkAccountType1ID = 11) UPDATE Account SET SeqID = SeqID + 1, ShippingAddressID = @AddressID WHERE ID = @LinkAccountID ; -- See if we need to insert a second Link IF (@LinkAccountType2ID IS NOT NULL) BEGIN SET @LinkCount = @LinkCount - 1; INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem] , [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName] , [IsOneTimeCompany]) VALUES ( @AddressLinkID + @LinkCount -- <ID, int,> , -1 -- ,<StoreID, int,> , 4002 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , 0 -- <IsMaster, bit,> , @LinkAccountID -- <ParentID, int,> , 2000 -- <ParentClassTypeID, smallint,> , @LinkAccountType2ID -- Billing Address -- <AddressTypeID, tinyint,> , @AddressID -- <AddressID, int,> , COALESCE(@AddressName, CASE @LinkAccountType2ID WHEN 10 THEN 'Billing' WHEN 11 THEN 'Shipping' ELSE 'Temporary' END) , @IsOneTimeAddress -- <IsOneTimeCompany, bit,> ); -- Delete the old Billing or Shipping Address Link if we are replacing it IF (@LinkAccountType2ID IN (10,11)) BEGIN DELETE FROM AddressLink WHERE ParentID = @LinkAccountID AND ParentClassTypeID = 2000 AND AddressTypeID = @LinkAccountType2ID AND AddressID != @AddressID; END; -- Update the Company if the Link is changing the Billing Address IF (@LinkAccountType2ID = 10) UPDATE Account SET SeqID = SeqID + 1, BillingAddressID = @AddressID WHERE ID = @LinkAccountID ; -- Update the Company if the Link is changing the Shipping Address IF (@LinkAccountType2ID = 11) UPDATE Account SET SeqID = SeqID + 1, ShippingAddressID = @AddressID WHERE ID = @LinkAccountID ; END; SET @IsMaster = 0; END; -- INSERT Contact Link ID if desired IF (@LinkContactID IS NOT NULL) BEGIN SET @LinkCount = @LinkCount - 1; INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName], [IsOneTimeCompany]) VALUES ( @AddressLinkID + @LinkCount -- <ID, int,> , -1 -- ,<StoreID, int,> , 4002 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @IsMaster -- <IsMaster, bit,> , @LinkContactID -- <ParentID, int,> , 3000 -- <ParentClassTypeID, smallint,> , @LinkContactType1ID -- Billing Address -- <AddressTypeID, tinyint,> , @AddressID -- <AddressID, int,> , COALESCE(@AddressName, CASE @LinkContactType1ID WHEN 10 THEN 'Billing' WHEN 11 THEN 'Shipping' ELSE 'Temporary' END) , @IsOneTimeAddress -- <IsOneTimeCompany, bit,> ); -- Delete the old Billing or Shipping Address Link if we are replacing it IF (@LinkContactType1ID IN (10,11)) BEGIN DELETE FROM AddressLink WHERE ParentID = @LinkContactID AND ParentClassTypeID = 3000 AND AddressTypeID = @LinkContactType1ID AND AddressID != @AddressID; END; -- Update the Contact if the Link is changing the Billing Address IF (@LinkContactType1ID = 10) UPDATE AccountContact SET SeqID = SeqID + 1, BillingAddressID = @AddressID WHERE ID = @LinkContactID ; -- Update the Company if the Link is changing the Shipping Address IF (@LinkContactType1ID = 11) UPDATE AccountContact SET SeqID = SeqID + 1, ShippingAddressID = @AddressID WHERE ID = @LinkContactID ; -- See if we need to insert a second Link IF (@LinkAccountType2ID IS NOT NULL) BEGIN SET @LinkCount = @LinkCount - 1; INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem] , [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName] , [IsOneTimeCompany]) VALUES ( @AddressLinkID + @LinkCount -- <ID, int,> , -1 -- ,<StoreID, int,> , 4002 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , 0 -- <IsMaster, bit,> , @LinkContactID -- <ParentID, int,> , 3000 -- <ParentClassTypeID, smallint,> , @LinkContactType2ID -- Billing Address -- <AddressTypeID, tinyint,> , @AddressID -- <AddressID, int,> , COALESCE(@AddressName, CASE @LinkContactType2ID WHEN 10 THEN 'Billing' WHEN 11 THEN 'Shipping' ELSE 'Temporary' END) , @IsOneTimeAddress -- <IsOneTimeCompany, bit,> ); -- Delete the old Billing or Shipping Address Link if we are replacing it IF (@LinkContactType2ID IN (10,11)) BEGIN DELETE FROM AddressLink WHERE ParentID = @LinkContactID AND ParentClassTypeID = 3000 AND AddressTypeID = @LinkContactType2ID AND AddressID != @AddressID; END; -- Update the Contact if the Link is changing the Billing Address IF (@LinkContactType2ID = 10) UPDATE AccountContact SET SeqID = SeqID + 1, BillingAddressID = @AddressID WHERE ID = @LinkContactID ; -- Update the Contact if the Link is changing the Shipping Address IF (@LinkContactType2ID = 11) UPDATE Account SET SeqID = SeqID + 1, ShippingAddressID = @AddressID WHERE ID = @LinkContactID ; END; SET @IsMaster = 0; END; -- INSERT TransHeader Link ID if desired IF (@LinkTHID IS NOT NULL) BEGIN SET @LinkCount = @LinkCount - 1; INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem] , [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName] , [IsOneTimeCompany]) VALUES ( @AddressLinkID + @LinkCount -- <ID, int,> , -1 -- ,<StoreID, int,> , 4002 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @IsMaster -- <IsMaster, bit,> , @LinkTHID -- <ParentID, int,> , 10000 -- <ParentClassTypeID, smallint,> , @LinkTHType1ID -- Billing Address -- <AddressTypeID, tinyint,> , @AddressID -- <AddressID, int,> , COALESCE(@AddressName, CASE @LinkTHType1ID WHEN 10 THEN 'Billing' WHEN 11 THEN 'Shipping' ELSE 'Temporary' END) , @IsOneTimeAddress -- <IsOneTimeCompany, bit,> ); -- Delete the old Billing or Shipping Address Link if we are replacing it IF (@LinkTHType1ID IN (10,11)) BEGIN DELETE FROM AddressLink WHERE ParentID = @LinkTHID AND ParentClassTypeID = 10000 AND AddressTypeID = @LinkTHType1ID AND AddressID != @AddressID; END; -- Update the Order if the Link is changing the Billing Address IF (@LinkTHType1ID = 10) BEGIN UPDATE TransHeader SET SeqID = SeqID + 1, InvoiceAddressID = @AddressID, InvoiceAddressLinkID = @AddressLinkID + @LinkCount WHERE ID = @LinkTHID; END; ; -- Update the Company if the Link is changing the Shipping Address IF (@LinkTHType1ID = 11) UPDATE TransHeader SET SeqID = SeqID + 1, ShippingAddressID = @AddressID, ShippingAddressLinkID = @AddressLinkID + @LinkCount WHERE ID = @LinkTHID ; -- See if we need to insert a second Link IF (@LinkTHType2ID IS NOT NULL) BEGIN SET @LinkCount = @LinkCount - 1; INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem] , [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName] , [IsOneTimeCompany]) VALUES ( @AddressLinkID + @LinkCount -- <ID, int,> , -1 -- ,<StoreID, int,> , 4002 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , 1 -- <IsMaster, bit,> , @LinkTHID -- <ParentID, int,> , 10000 -- <ParentClassTypeID, smallint,> , @LinkTHType2ID -- Billing Address -- <AddressTypeID, tinyint,> , @AddressID -- <AddressID, int,> , COALESCE(@AddressName, CASE @LinkTHType2ID WHEN 10 THEN 'Billing' WHEN 11 THEN 'Shipping' ELSE 'Temporary' END) , @IsOneTimeAddress -- <IsOneTimeCompany, bit,> ); -- Delete the old Billing or Shipping Address Link if we are replacing it IF (@LinkTHType2ID IN (10,11)) BEGIN DELETE FROM AddressLink WHERE ParentID = @LinkTHID AND ParentClassTypeID = 10000 AND AddressTypeID = @LinkTHType2ID AND AddressID != @AddressID; END; -- Update the Order if the Link is changing the Billing Address IF (@LinkTHType2ID = 10) UPDATE TransHeader SET SeqID = SeqID + 1, InvoiceAddressID = @AddressID, InvoiceAddressLinkID = @AddressLinkID + @LinkCount WHERE ID = @LinkTHID ; -- Update the Company if the Link is changing the Shipping Address IF (@LinkTHType2ID = 11) UPDATE TransHeader SET SeqID = SeqID + 1, ShippingAddressID = @AddressID, ShippingAddressLinkID = @AddressLinkID + @LinkCount WHERE ID = @LinkTHID ; END; SET @IsMaster = 0; END; -- INSERT Shipment Link ID if desired IF (@LinkShipmentID IS NOT NULL) BEGIN SET @LinkCount = @LinkCount - 1; INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName], [IsOneTimeCompany]) VALUES ( @AddressLinkID + @LinkCount -- <ID, int,> , -1 -- ,<StoreID, int,> , 4002 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @IsMaster -- <IsMaster, bit,> , @LinkShipmentID -- <ParentID, int,> , 10700 -- <ParentClassTypeID, smallint,> , @LinkShipmentType1ID -- Billing Address -- <AddressTypeID, tinyint,> , @AddressID -- <AddressID, int,> , COALESCE(@AddressName, CASE @LinkShipmentType1ID WHEN 10 THEN 'Billing' WHEN 11 THEN 'Shipping' ELSE 'Temporary' END) , @IsOneTimeAddress -- <IsOneTimeCompany, bit,> ); END; -- Now commit the Transaction COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION; DECLARE @ErrorMessage VARCHAR(2048); DECLARE @ErrorNumber INT; DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; DECLARE @ErrorLine INT; DECLARE @ErrorProcedure VARCHAR(200); SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE(), @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-'); -- Build the message string that will contain original -- error information. SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: '+ ERROR_MESSAGE(); -- Raise an error: msg_str parameter of RAISERROR will contain -- the original error information. RAISERROR ( @ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, -- parameter: original error number. @ErrorSeverity, -- parameter: original error severity. @ErrorState, -- parameter: original error state. @ErrorProcedure, -- parameter: original error procedure name. @ErrorLine -- parameter: original error line number. ); END CATCH; -- -- Step 4. Refresh the Records -- IF (@RefreshOnSave=1) BEGIN IF (@LinkTHID IS NOT NULL) EXEC dbo.csf_chapi_refresh @LinkTHID, 10000, -1; IF (@LinkAccountID IS NOT NULL) EXEC dbo.csf_chapi_refresh @LinkAccountID, 2000, -1; IF (@LinkContactID IS NOT NULL) EXEC dbo.csf_chapi_refresh @LinkContactID, 3000, -1; IF (@LinkShipmentID IS NOT NULL) EXEC dbo.csf_chapi_refresh @LinkShipmentID, 10700, -1; END; -- -- Step 5. Return the New Detail ID -- SELECT @AddressID, 4001, @AddressLinkID, 4002 END;
Source
Contributor: Cyrious Software
Date: 5/2016
Version: Control 5.7+
You could leave a comment if you were logged in.