JDBC Test Step SQL Server query not running entirely
I'm using this query in a JDBC Test Step with SQL Server, but it seems to be not executing all the lines of the query.
My tests are failing because of that, then i have to execute the querys over SQL Server Management Studio, then i get all the right results and the tests pass.
Is it a bug or is there a limit of lines to execute a JDBC step?
This is happening on SoapUI 5.0 and SoapUI Pro 5.1.2.
BEGIN
DECLARE @ApplicationName VARCHAR(50) = 'Test Application'
DECLARE @UserID INT = 2
DECLARE @ShowLevel2Messages BIT = 1
DECLARE @TagID INT = 1 -- 17 For Level 1 Messages
DECLARE @MessageCode VARCHAR(20) = '404' -- Can be NULL
DECLARE @MessageStatus TINYINT = 3 -- Can be NULL
DECLARE @ShowLevel TINYINT = 0 -- Can be NULL
DECLARE @DestinationICAO VARCHAR(3) = 'JAM'
DECLARE @ConstraintName VARCHAR(10) = 'JAM-010'
DECLARE @Enabled BIT = 1
DECLARE @ManualCheckRequired BIT = 0
DECLARE @OverrideVisaRequirement BIT = 0
DECLARE @OverridePassportValidity BIT = 0
DECLARE @OverrideAcceptedDocuments BIT = 0
DECLARE @DefaultMessage VARCHAR(4000) = 'Constraint - Lvl 1 - Age Equal To 18.'
DECLARE @Status TINYINT = 3
DECLARE @MatchType TINYINT = 0
DECLARE @Direction TINYINT = 0
DECLARE @StartDate DATETIME = '1990-01-01'
DECLARE @EndDate DATETIME = '9999-12-31'
DECLARE @NationalityOperator TINYINT = 0
DECLARE @Nationalities VARCHAR(1000) = 'GBR,USA,ZAF'
DECLARE @FieldID INT = 9
DECLARE @ConstraintOperator TINYINT = 0
DECLARE @ConstraintValue1 NVARCHAR(4000)= '18'
DECLARE @ConstraintValue2 NVARCHAR(4000)= NULL
DECLARE @VR_PassportICAOs VARCHAR(1000) = 'GBR,USA,ZAF'
DECLARE @VR_VisaRequired BIT = 0
DECLARE @VR_WaiverPeriod TINYINT = 3
DECLARE @VR_WaiverAmount INT = 3
DECLARE @VR_TransitVisaRequired BIT = 0
DECLARE @VR_ValiditStart TINYINT = 1
DECLARE @VR_ValidityPeriod TINYINT = 1
DECLARE @VR_ValidityAmount INT = 1
DECLARE @OutboundDestinationICAO VARCHAR(3) = NULL
-- DELETE OLD APPLICATION DATA
DELETE FROM ApplicationTag WHERE ApplicationID IN (SELECT ApplicationID FROM [Application] WHERE ApplicationName = @ApplicationName)
DELETE FROM [Application] WHERE ApplicationName = @ApplicationName
-- DELETE OLD CONSTRAINT DATA
DELETE FROM ConstraintTag WHERE ConstraintID IN (SELECT ConstraintID FROM [Constraint] WHERE ConstraintName = @ConstraintName)
DELETE FROM ConstraintField WHERE ConstraintID IN (SELECT ConstraintID FROM [Constraint] WHERE ConstraintName = @ConstraintName)
DELETE FROM ConstraintIssuingCountry WHERE ConstraintID IN (SELECT ConstraintID FROM [Constraint] WHERE ConstraintName = @ConstraintName)
DELETE FROM ConstraintNationality WHERE ConstraintID IN (SELECT ConstraintID FROM [Constraint] WHERE ConstraintName = @ConstraintName)
DELETE FROM ConstraintMessage WHERE ConstraintID IN (SELECT ConstraintID FROM [Constraint] WHERE ConstraintName = @ConstraintName)
DELETE FROM ConstraintNationality_Normalized WHERE ConstraintID IN (SELECT ConstraintID FROM [Constraint] WHERE ConstraintName = @ConstraintName)
DELETE FROM [Constraint] WHERE ConstraintName = @ConstraintName
IF @VR_PassportICAOs IS NULL
BEGIN
DELETE FROM [dbo].[VisaRequirementAcceptedDocument] WHERE VisaRequirementID =
(SELECT VisaRequirementID FROM [dbo].[VisaRequirement] WHERE [DestinationICAO] = @DestinationICAO AND [PassportICAO] = @DestinationICAO)
DELETE FROM [dbo].[VisaRequirement] WHERE [DestinationICAO] = @DestinationICAO AND [PassportICAO] = @DestinationICAO
END
ELSE
BEGIN
DELETE FROM [dbo].[VisaRequirementAcceptedDocument] WHERE VisaRequirementID IN
(SELECT VisaRequirementID FROM [dbo].[VisaRequirement] WHERE [DestinationICAO] = @DestinationICAO AND [PassportICAO] IN (SELECT StringPart FROM dbo.SPLIT(@VR_PassportICAOs, ',', 0)))
DELETE FROM [dbo].[VisaRequirement] WHERE [DestinationICAO] = @DestinationICAO AND [PassportICAO] IN (SELECT StringPart FROM dbo.SPLIT(@VR_PassportICAOs, ',', 0))
END
-- INSERT THE APPLICATION
DECLARE @ApplicationID INT
SELECT @ApplicationID = MAX(ApplicationID) + 1 FROM dbo.[Application]
SELECT @ApplicationID = CASE WHEN @ApplicationID IS NULL THEN 1 ELSE @ApplicationID END
INSERT INTO dbo.[Application] (ApplicationID, ApplicationName, TagHandleOptions, CreationDate, LastUpdate, Deleted)
VALUES (@ApplicationID, @ApplicationName, @ShowLevel2Messages, GETDATE(), GETDATE(), 0)
INSERT INTO dbo.[ApplicationTag] (ApplicationID, TagID, MessageCode, MessageStatus, ShowLevel)
VALUES (@ApplicationID, @TagID, @MessageCode, @MessageStatus, @ShowLevel)
-- TRY TO UPDATE THE USER
IF EXISTS (SELECT * FROM TDoc_Data_V5_UnitTests.dbo.[User] WHERE UserID = @UserID)
UPDATE TDoc_Data_V5_UnitTests.dbo.[User]
SET ApplicationID = @ApplicationID
WHERE UserID = @UserID
-- INSERT THE CONSTRAINT
DECLARE @ConstraintID INT
SELECT @ConstraintID = MAX(ConstraintID) + 1 FROM dbo.[Constraint]
SELECT @ConstraintID = CASE WHEN @ConstraintID IS NULL THEN 1 ELSE @ConstraintID END
INSERT INTO [Constraint] (
ConstraintID, DestinationICAO, ConstraintName, DefaultMessage, [Status], [Enabled], ManualCheckRequired,
OverrideVisaRequirement, OverridePassportValidity, OverrideAcceptedDocuments, MatchType, NationalityOperator,
IssuingCountryOperator, StartDate, EndDate, Direction, [Priority], Ordinal, CreationDate, LastUpdate, Deleted
) VALUES (
@ConstraintID, @DestinationICAO, @ConstraintName, @DefaultMessage, @Status, @Enabled, @ManualCheckRequired,
@OverrideVisaRequirement, @OverridePassportValidity, @OverrideAcceptedDocuments, @MatchType, @NationalityOperator,
0, @StartDate, @EndDate, @Direction, 0, 100, GETDATE(), GETDATE(), 0
)
-- INSERT THE CONSTRAINT NATIONALITIES
INSERT INTO dbo.ConstraintNationality (ConstraintID, NationalityICAO, CreationDate, LastUpdate, Deleted)
SELECT @ConstraintID, StringPart, GETDATE(), GETDATE(), 0 FROM dbo.SPLIT(@Nationalities, ',', 0)
-- INSERT THE CONSTAINT FIELD
IF @FieldID IS NOT NULL
BEGIN
DECLARE @ConstraintFieldID INT
SELECT @ConstraintFieldID = MAX(ConstraintFieldID) + 1 FROM dbo.ConstraintField
SELECT @ConstraintFieldID = CASE WHEN @ConstraintFieldID IS NULL THEN 1 ELSE @ConstraintFieldID END
INSERT INTO dbo.ConstraintField (
ConstraintFieldID, ConstraintID, FieldID, ConstraintOperator, ConstraintValue1, ConstraintValue2
) VALUES (
@ConstraintFieldID, @ConstraintID, @FieldID, @ConstraintOperator, @ConstraintValue1, @ConstraintValue2
)
END
-- INSERT THE CONSTRAINT TAG
INSERT INTO dbo.ConstraintTag (ConstraintID, TagID, CreationDate, LastUpdate, Deleted)
VALUES (@ConstraintID, @TagID, GETDATE(), GETDATE(), 0)
-- INSERT THE ACCEPTED DOCUMENTS
DECLARE @VisaRequirementID INT, @VisaRequirementAcceptedDocumentID INT
SELECT @VisaRequirementID = MAX(VisaRequirementID) + 1 FROM dbo.[VisaRequirement]
SELECT @VisaRequirementID = CASE WHEN @VisaRequirementID IS NULL THEN 1 ELSE @VisaRequirementID END
SELECT @VisaRequirementAcceptedDocumentID = MAX(VisaRequirementAcceptedDocumentID) + 1 FROM VisaRequirementAcceptedDocument
SELECT @VisaRequirementAcceptedDocumentID = CASE WHEN @VisaRequirementAcceptedDocumentID IS NULL THEN 1 ELSE @VisaRequirementAcceptedDocumentID END
IF @VR_PassportICAOs IS NULL
BEGIN
INSERT INTO [dbo].[VisaRequirement] (
[VisaRequirementID], [PassportICAO], [DestinationICAO], [VisaRequired], [VisaWaiverPeriod],
[VisaWaiverAmount], [TransitVisaRequired], [ValidityStart], [ValidityPeriod],
[ValidityAmount], [CreationDate], [LastUpdate], [Deleted]
) VALUES (
@VisaRequirementID, @DestinationICAO, @DestinationICAO, @VR_VisaRequired, @VR_WaiverPeriod,
@VR_WaiverAmount, @VR_TransitVisaRequired, @VR_ValiditStart, @VR_ValidityPeriod,
@VR_ValidityAmount, GETDATE(), GETDATE(), 0
)
INSERT INTO [dbo].VisaRequirementAcceptedDocument (
VisaRequirementAcceptedDocumentID, VisaRequirementID, AcceptedDocumentID, CreationDate, LastUpdate, Deleted
) VALUES (
@VisaRequirementAcceptedDocumentID, @VisaRequirementID, 1, GETDATE(), GETDATE(), 0
)
END
ELSE
BEGIN
SET @VisaRequirementID = @VisaRequirementID - 1
INSERT INTO [dbo].[VisaRequirement] (
[VisaRequirementID], [PassportICAO], [DestinationICAO], [VisaRequired], [VisaWaiverPeriod],
[VisaWaiverAmount], [TransitVisaRequired], [ValidityStart], [ValidityPeriod],
[ValidityAmount], [CreationDate], [LastUpdate], [Deleted]
)
SELECT @VisaRequirementID + ROW_NUMBER() OVER (ORDER BY StringPart),
StringPart, @DestinationICAO, @VR_VisaRequired, @VR_WaiverPeriod,
@VR_WaiverAmount, @VR_TransitVisaRequired, @VR_ValiditStart, @VR_ValidityPeriod,
@VR_ValidityAmount, GETDATE(), GETDATE(), 0
FROM dbo.SPLIT(@VR_PassportICAOs, ',', 0)
SET @VisaRequirementAcceptedDocumentID = @VisaRequirementAcceptedDocumentID - 1
INSERT INTO [dbo].VisaRequirementAcceptedDocument (
VisaRequirementAcceptedDocumentID, VisaRequirementID, AcceptedDocumentID, CreationDate,
LastUpdate, Deleted
)
SELECT @VisaRequirementAcceptedDocumentID + ROW_NUMBER() OVER (ORDER BY StringPart),
@VisaRequirementID + ROW_NUMBER() OVER (ORDER BY StringPart), 1, GETDATE(), GETDATE(), 0
FROM dbo.SPLIT(@VR_PassportICAOs, ',', 0)
END
IF @OutboundDestinationICAO IS NOT NULL
BEGIN
IF @VR_PassportICAOs IS NULL
BEGIN
INSERT INTO [dbo].[VisaRequirement] (
[VisaRequirementID], [PassportICAO], [DestinationICAO], [VisaRequired], [VisaWaiverPeriod],
[VisaWaiverAmount], [TransitVisaRequired], [ValidityStart], [ValidityPeriod],
[ValidityAmount], [CreationDate], [LastUpdate], [Deleted]
) VALUES (
@VisaRequirementID + 1, @DestinationICAO, @OutboundDestinationICAO, @VR_VisaRequired,
@VR_WaiverPeriod, @VR_WaiverAmount, @VR_TransitVisaRequired, @VR_ValiditStart,
@VR_ValidityPeriod, @VR_ValidityAmount, GETDATE(), GETDATE(), 0
)
INSERT INTO [dbo].VisaRequirementAcceptedDocument (
VisaRequirementAcceptedDocumentID, VisaRequirementID, AcceptedDocumentID, CreationDate,
LastUpdate, Deleted
) VALUES (
@VisaRequirementAcceptedDocumentID, @VisaRequirementID + 1, 1, GETDATE(), GETDATE(), 0
)
END
ELSE
BEGIN
INSERT INTO [dbo].[VisaRequirement] (
[VisaRequirementID], [PassportICAO], [DestinationICAO], [VisaRequired], [VisaWaiverPeriod],
[VisaWaiverAmount], [TransitVisaRequired], [ValidityStart], [ValidityPeriod],
[ValidityAmount], [CreationDate], [LastUpdate], [Deleted]
)
SELECT @VisaRequirementID + ROW_NUMBER() OVER (ORDER BY StringPart), StringPart,
@OutboundDestinationICAO, @VR_VisaRequired, @VR_WaiverPeriod, @VR_WaiverAmount,
@VR_TransitVisaRequired, @VR_ValiditStart, @VR_ValidityPeriod, @VR_ValidityAmount,
GETDATE(), GETDATE(), 0
FROM dbo.SPLIT(@VR_PassportICAOs, ',', 0)
INSERT INTO [dbo].VisaRequirementAcceptedDocument (
VisaRequirementAcceptedDocumentID, VisaRequirementID, AcceptedDocumentID, CreationDate,
LastUpdate, Deleted
)
SELECT @VisaRequirementAcceptedDocumentID + ROW_NUMBER() OVER (ORDER BY StringPart),
@VisaRequirementID + ROW_NUMBER() OVER (ORDER BY StringPart), 1, GETDATE(), GETDATE(), 0
FROM dbo.SPLIT(@VR_PassportICAOs, ',', 0)
END
END
DECLARE @CountryID INT
SELECT @CountryID = CountryID FROM dbo.Country WHERE ICAO = @DestinationICAO
EXEC dbo.stp_Constraints_Recalculate_ByDestinationCountryID @CountryID
IF @OutboundDestinationICAO IS NOT NULL
BEGIN
SELECT @CountryID = CountryID FROM dbo.Country WHERE ICAO = @OutboundDestinationICAO
EXEC dbo.stp_Constraints_Recalculate_ByDestinationCountryID @CountryID
END
END
My tests are failing because of that, then i have to execute the querys over SQL Server Management Studio, then i get all the right results and the tests pass.
Is it a bug or is there a limit of lines to execute a JDBC step?
This is happening on SoapUI 5.0 and SoapUI Pro 5.1.2.
BEGIN
DECLARE @ApplicationName VARCHAR(50) = 'Test Application'
DECLARE @UserID INT = 2
DECLARE @ShowLevel2Messages BIT = 1
DECLARE @TagID INT = 1 -- 17 For Level 1 Messages
DECLARE @MessageCode VARCHAR(20) = '404' -- Can be NULL
DECLARE @MessageStatus TINYINT = 3 -- Can be NULL
DECLARE @ShowLevel TINYINT = 0 -- Can be NULL
DECLARE @DestinationICAO VARCHAR(3) = 'JAM'
DECLARE @ConstraintName VARCHAR(10) = 'JAM-010'
DECLARE @Enabled BIT = 1
DECLARE @ManualCheckRequired BIT = 0
DECLARE @OverrideVisaRequirement BIT = 0
DECLARE @OverridePassportValidity BIT = 0
DECLARE @OverrideAcceptedDocuments BIT = 0
DECLARE @DefaultMessage VARCHAR(4000) = 'Constraint - Lvl 1 - Age Equal To 18.'
DECLARE @Status TINYINT = 3
DECLARE @MatchType TINYINT = 0
DECLARE @Direction TINYINT = 0
DECLARE @StartDate DATETIME = '1990-01-01'
DECLARE @EndDate DATETIME = '9999-12-31'
DECLARE @NationalityOperator TINYINT = 0
DECLARE @Nationalities VARCHAR(1000) = 'GBR,USA,ZAF'
DECLARE @FieldID INT = 9
DECLARE @ConstraintOperator TINYINT = 0
DECLARE @ConstraintValue1 NVARCHAR(4000)= '18'
DECLARE @ConstraintValue2 NVARCHAR(4000)= NULL
DECLARE @VR_PassportICAOs VARCHAR(1000) = 'GBR,USA,ZAF'
DECLARE @VR_VisaRequired BIT = 0
DECLARE @VR_WaiverPeriod TINYINT = 3
DECLARE @VR_WaiverAmount INT = 3
DECLARE @VR_TransitVisaRequired BIT = 0
DECLARE @VR_ValiditStart TINYINT = 1
DECLARE @VR_ValidityPeriod TINYINT = 1
DECLARE @VR_ValidityAmount INT = 1
DECLARE @OutboundDestinationICAO VARCHAR(3) = NULL
-- DELETE OLD APPLICATION DATA
DELETE FROM ApplicationTag WHERE ApplicationID IN (SELECT ApplicationID FROM [Application] WHERE ApplicationName = @ApplicationName)
DELETE FROM [Application] WHERE ApplicationName = @ApplicationName
-- DELETE OLD CONSTRAINT DATA
DELETE FROM ConstraintTag WHERE ConstraintID IN (SELECT ConstraintID FROM [Constraint] WHERE ConstraintName = @ConstraintName)
DELETE FROM ConstraintField WHERE ConstraintID IN (SELECT ConstraintID FROM [Constraint] WHERE ConstraintName = @ConstraintName)
DELETE FROM ConstraintIssuingCountry WHERE ConstraintID IN (SELECT ConstraintID FROM [Constraint] WHERE ConstraintName = @ConstraintName)
DELETE FROM ConstraintNationality WHERE ConstraintID IN (SELECT ConstraintID FROM [Constraint] WHERE ConstraintName = @ConstraintName)
DELETE FROM ConstraintMessage WHERE ConstraintID IN (SELECT ConstraintID FROM [Constraint] WHERE ConstraintName = @ConstraintName)
DELETE FROM ConstraintNationality_Normalized WHERE ConstraintID IN (SELECT ConstraintID FROM [Constraint] WHERE ConstraintName = @ConstraintName)
DELETE FROM [Constraint] WHERE ConstraintName = @ConstraintName
IF @VR_PassportICAOs IS NULL
BEGIN
DELETE FROM [dbo].[VisaRequirementAcceptedDocument] WHERE VisaRequirementID =
(SELECT VisaRequirementID FROM [dbo].[VisaRequirement] WHERE [DestinationICAO] = @DestinationICAO AND [PassportICAO] = @DestinationICAO)
DELETE FROM [dbo].[VisaRequirement] WHERE [DestinationICAO] = @DestinationICAO AND [PassportICAO] = @DestinationICAO
END
ELSE
BEGIN
DELETE FROM [dbo].[VisaRequirementAcceptedDocument] WHERE VisaRequirementID IN
(SELECT VisaRequirementID FROM [dbo].[VisaRequirement] WHERE [DestinationICAO] = @DestinationICAO AND [PassportICAO] IN (SELECT StringPart FROM dbo.SPLIT(@VR_PassportICAOs, ',', 0)))
DELETE FROM [dbo].[VisaRequirement] WHERE [DestinationICAO] = @DestinationICAO AND [PassportICAO] IN (SELECT StringPart FROM dbo.SPLIT(@VR_PassportICAOs, ',', 0))
END
-- INSERT THE APPLICATION
DECLARE @ApplicationID INT
SELECT @ApplicationID = MAX(ApplicationID) + 1 FROM dbo.[Application]
SELECT @ApplicationID = CASE WHEN @ApplicationID IS NULL THEN 1 ELSE @ApplicationID END
INSERT INTO dbo.[Application] (ApplicationID, ApplicationName, TagHandleOptions, CreationDate, LastUpdate, Deleted)
VALUES (@ApplicationID, @ApplicationName, @ShowLevel2Messages, GETDATE(), GETDATE(), 0)
INSERT INTO dbo.[ApplicationTag] (ApplicationID, TagID, MessageCode, MessageStatus, ShowLevel)
VALUES (@ApplicationID, @TagID, @MessageCode, @MessageStatus, @ShowLevel)
-- TRY TO UPDATE THE USER
IF EXISTS (SELECT * FROM TDoc_Data_V5_UnitTests.dbo.[User] WHERE UserID = @UserID)
UPDATE TDoc_Data_V5_UnitTests.dbo.[User]
SET ApplicationID = @ApplicationID
WHERE UserID = @UserID
-- INSERT THE CONSTRAINT
DECLARE @ConstraintID INT
SELECT @ConstraintID = MAX(ConstraintID) + 1 FROM dbo.[Constraint]
SELECT @ConstraintID = CASE WHEN @ConstraintID IS NULL THEN 1 ELSE @ConstraintID END
INSERT INTO [Constraint] (
ConstraintID, DestinationICAO, ConstraintName, DefaultMessage, [Status], [Enabled], ManualCheckRequired,
OverrideVisaRequirement, OverridePassportValidity, OverrideAcceptedDocuments, MatchType, NationalityOperator,
IssuingCountryOperator, StartDate, EndDate, Direction, [Priority], Ordinal, CreationDate, LastUpdate, Deleted
) VALUES (
@ConstraintID, @DestinationICAO, @ConstraintName, @DefaultMessage, @Status, @Enabled, @ManualCheckRequired,
@OverrideVisaRequirement, @OverridePassportValidity, @OverrideAcceptedDocuments, @MatchType, @NationalityOperator,
0, @StartDate, @EndDate, @Direction, 0, 100, GETDATE(), GETDATE(), 0
)
-- INSERT THE CONSTRAINT NATIONALITIES
INSERT INTO dbo.ConstraintNationality (ConstraintID, NationalityICAO, CreationDate, LastUpdate, Deleted)
SELECT @ConstraintID, StringPart, GETDATE(), GETDATE(), 0 FROM dbo.SPLIT(@Nationalities, ',', 0)
-- INSERT THE CONSTAINT FIELD
IF @FieldID IS NOT NULL
BEGIN
DECLARE @ConstraintFieldID INT
SELECT @ConstraintFieldID = MAX(ConstraintFieldID) + 1 FROM dbo.ConstraintField
SELECT @ConstraintFieldID = CASE WHEN @ConstraintFieldID IS NULL THEN 1 ELSE @ConstraintFieldID END
INSERT INTO dbo.ConstraintField (
ConstraintFieldID, ConstraintID, FieldID, ConstraintOperator, ConstraintValue1, ConstraintValue2
) VALUES (
@ConstraintFieldID, @ConstraintID, @FieldID, @ConstraintOperator, @ConstraintValue1, @ConstraintValue2
)
END
-- INSERT THE CONSTRAINT TAG
INSERT INTO dbo.ConstraintTag (ConstraintID, TagID, CreationDate, LastUpdate, Deleted)
VALUES (@ConstraintID, @TagID, GETDATE(), GETDATE(), 0)
-- INSERT THE ACCEPTED DOCUMENTS
DECLARE @VisaRequirementID INT, @VisaRequirementAcceptedDocumentID INT
SELECT @VisaRequirementID = MAX(VisaRequirementID) + 1 FROM dbo.[VisaRequirement]
SELECT @VisaRequirementID = CASE WHEN @VisaRequirementID IS NULL THEN 1 ELSE @VisaRequirementID END
SELECT @VisaRequirementAcceptedDocumentID = MAX(VisaRequirementAcceptedDocumentID) + 1 FROM VisaRequirementAcceptedDocument
SELECT @VisaRequirementAcceptedDocumentID = CASE WHEN @VisaRequirementAcceptedDocumentID IS NULL THEN 1 ELSE @VisaRequirementAcceptedDocumentID END
IF @VR_PassportICAOs IS NULL
BEGIN
INSERT INTO [dbo].[VisaRequirement] (
[VisaRequirementID], [PassportICAO], [DestinationICAO], [VisaRequired], [VisaWaiverPeriod],
[VisaWaiverAmount], [TransitVisaRequired], [ValidityStart], [ValidityPeriod],
[ValidityAmount], [CreationDate], [LastUpdate], [Deleted]
) VALUES (
@VisaRequirementID, @DestinationICAO, @DestinationICAO, @VR_VisaRequired, @VR_WaiverPeriod,
@VR_WaiverAmount, @VR_TransitVisaRequired, @VR_ValiditStart, @VR_ValidityPeriod,
@VR_ValidityAmount, GETDATE(), GETDATE(), 0
)
INSERT INTO [dbo].VisaRequirementAcceptedDocument (
VisaRequirementAcceptedDocumentID, VisaRequirementID, AcceptedDocumentID, CreationDate, LastUpdate, Deleted
) VALUES (
@VisaRequirementAcceptedDocumentID, @VisaRequirementID, 1, GETDATE(), GETDATE(), 0
)
END
ELSE
BEGIN
SET @VisaRequirementID = @VisaRequirementID - 1
INSERT INTO [dbo].[VisaRequirement] (
[VisaRequirementID], [PassportICAO], [DestinationICAO], [VisaRequired], [VisaWaiverPeriod],
[VisaWaiverAmount], [TransitVisaRequired], [ValidityStart], [ValidityPeriod],
[ValidityAmount], [CreationDate], [LastUpdate], [Deleted]
)
SELECT @VisaRequirementID + ROW_NUMBER() OVER (ORDER BY StringPart),
StringPart, @DestinationICAO, @VR_VisaRequired, @VR_WaiverPeriod,
@VR_WaiverAmount, @VR_TransitVisaRequired, @VR_ValiditStart, @VR_ValidityPeriod,
@VR_ValidityAmount, GETDATE(), GETDATE(), 0
FROM dbo.SPLIT(@VR_PassportICAOs, ',', 0)
SET @VisaRequirementAcceptedDocumentID = @VisaRequirementAcceptedDocumentID - 1
INSERT INTO [dbo].VisaRequirementAcceptedDocument (
VisaRequirementAcceptedDocumentID, VisaRequirementID, AcceptedDocumentID, CreationDate,
LastUpdate, Deleted
)
SELECT @VisaRequirementAcceptedDocumentID + ROW_NUMBER() OVER (ORDER BY StringPart),
@VisaRequirementID + ROW_NUMBER() OVER (ORDER BY StringPart), 1, GETDATE(), GETDATE(), 0
FROM dbo.SPLIT(@VR_PassportICAOs, ',', 0)
END
IF @OutboundDestinationICAO IS NOT NULL
BEGIN
IF @VR_PassportICAOs IS NULL
BEGIN
INSERT INTO [dbo].[VisaRequirement] (
[VisaRequirementID], [PassportICAO], [DestinationICAO], [VisaRequired], [VisaWaiverPeriod],
[VisaWaiverAmount], [TransitVisaRequired], [ValidityStart], [ValidityPeriod],
[ValidityAmount], [CreationDate], [LastUpdate], [Deleted]
) VALUES (
@VisaRequirementID + 1, @DestinationICAO, @OutboundDestinationICAO, @VR_VisaRequired,
@VR_WaiverPeriod, @VR_WaiverAmount, @VR_TransitVisaRequired, @VR_ValiditStart,
@VR_ValidityPeriod, @VR_ValidityAmount, GETDATE(), GETDATE(), 0
)
INSERT INTO [dbo].VisaRequirementAcceptedDocument (
VisaRequirementAcceptedDocumentID, VisaRequirementID, AcceptedDocumentID, CreationDate,
LastUpdate, Deleted
) VALUES (
@VisaRequirementAcceptedDocumentID, @VisaRequirementID + 1, 1, GETDATE(), GETDATE(), 0
)
END
ELSE
BEGIN
INSERT INTO [dbo].[VisaRequirement] (
[VisaRequirementID], [PassportICAO], [DestinationICAO], [VisaRequired], [VisaWaiverPeriod],
[VisaWaiverAmount], [TransitVisaRequired], [ValidityStart], [ValidityPeriod],
[ValidityAmount], [CreationDate], [LastUpdate], [Deleted]
)
SELECT @VisaRequirementID + ROW_NUMBER() OVER (ORDER BY StringPart), StringPart,
@OutboundDestinationICAO, @VR_VisaRequired, @VR_WaiverPeriod, @VR_WaiverAmount,
@VR_TransitVisaRequired, @VR_ValiditStart, @VR_ValidityPeriod, @VR_ValidityAmount,
GETDATE(), GETDATE(), 0
FROM dbo.SPLIT(@VR_PassportICAOs, ',', 0)
INSERT INTO [dbo].VisaRequirementAcceptedDocument (
VisaRequirementAcceptedDocumentID, VisaRequirementID, AcceptedDocumentID, CreationDate,
LastUpdate, Deleted
)
SELECT @VisaRequirementAcceptedDocumentID + ROW_NUMBER() OVER (ORDER BY StringPart),
@VisaRequirementID + ROW_NUMBER() OVER (ORDER BY StringPart), 1, GETDATE(), GETDATE(), 0
FROM dbo.SPLIT(@VR_PassportICAOs, ',', 0)
END
END
DECLARE @CountryID INT
SELECT @CountryID = CountryID FROM dbo.Country WHERE ICAO = @DestinationICAO
EXEC dbo.stp_Constraints_Recalculate_ByDestinationCountryID @CountryID
IF @OutboundDestinationICAO IS NOT NULL
BEGIN
SELECT @CountryID = CountryID FROM dbo.Country WHERE ICAO = @OutboundDestinationICAO
EXEC dbo.stp_Constraints_Recalculate_ByDestinationCountryID @CountryID
END
END