this is my stored procedure here I am facing Exception of Invalid cast, kindly suggest...
USE [bomgen_UTC_PROD]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [bomgen_Admin].[utc_NewProcessImportData]
(
@ImportStagingId INT,
@EffectiveDate DATE,
@VersionName NVARCHAR(1000),
@VersionNotes NVARCHAR(1000)
)
AS
BEGIN
SET NOCOUNT ON;
--BEGIN TRANSACTION ProcessImportDataTransaction;
BEGIN TRY
DECLARE @TxnCountLimit INT, @TxnCount INT;
SELECT @TxnCountLimit = 500;
SELECT @TxnCount = 0;
DECLARE @VersionId INT, @NewPartId INT, @NewPartDetailId INT, @MatchingPartDetailId INT, @CategoryId INT;
DECLARE @PartNumber NVARCHAR(200), @RowNumber INT, @PartId INT;
DECLARE @FullDescription NVARCHAR(MAX), @ShortDescription NVARCHAR(MAX), @ShippingWeightLB DECIMAL(10,2), @ShippingWeightKG DECIMAL(10,2);
DECLARE @ListPrice MONEY, @ListPriceText NVARCHAR(MAX), @PriceLists NVARCHAR(100), @CountryOfOrigin NVARCHAR(3);
DECLARE @idx INT, @lidx INT;
DECLARE @CheckCat NVARCHAR(200);
DECLARE @PriorVersionId INT, @PriorHazardVersionId INT;
-- create a new version
INSERT INTO Version (EffectiveDate, VersionName, VersionNotes, PublishedFlag)
VALUES (@EffectiveDate, @VersionName, @VersionNotes, 0);
SELECT @VersionId = SCOPE_IDENTITY();
-- copy the hazard versions;
SELECT @PriorHazardVersionId = MAX(VersionId)
FROM HazardVersion;
IF (@PriorHazardVersionId IS NOT NULL)
BEGIN
PRINT 'previous hazard version=' + cast(@PriorHazardVersionId as varchar(100))
INSERT INTO HazardVersion (HazardId, AgentId, VersionId)
SELECT hv.HazardId
, hv.AgentId
, @VersionId
FROM HazardVersion hv
WHERE hv.VersionId = @PriorHazardVersionId;
END;
ELSE
BEGIN
PRINT 'previous hazard version is null so=' + cast(@versionId as varchar(100))
INSERT INTO HazardVersion (HazardId, AgentId, VersionId)
SELECT h.HazardId
, h.AgentId
, @VersionId
FROM Hazard h;
END;
DECLARE NewPartCursor CURSOR FOR
SELECT LTRIM(RTRIM(isd.PartNumber)) AS PartNumber
, LTRIM(RTRIM(isd.FullDescription)) AS FullDescription
, LTRIM(RTRIM(isd.ShortDescription)) AS ShortDescription
, CASE WHEN ISNUMERIC(LTRIM(RTRIM(isd.ShippingWeightLB))) = 1 THEN CAST(LTRIM(RTRIM(REPLACE(isd.ShippingWeightLB, ',', ''))) AS DECIMAL(10,2)) ELSE NULL END AS ShippingWeightLB
, CASE WHEN ISNUMERIC(LTRIM(RTRIM(isd.ShippingWeightKG))) = 1 THEN CAST(LTRIM(RTRIM(REPLACE(isd.ShippingWeightKG, ',', ''))) AS DECIMAL(10,2)) ELSE NULL END AS ShippingWeightKG
, CASE WHEN ISNUMERIC(LTRIM(RTRIM(isd.ListPriceText))) = 1 THEN CAST(LTRIM(RTRIM(isd.ListPriceText)) AS MONEY) ELSE NULL END AS ListPrice
, LTRIM(RTRIM(ListPriceText)) AS ListPriceText
, LTRIM(RTRIM(isd.PriceLists)) AS PriceLists
, LTRIM(RTRIM(isd.CountryOfOrigin)) AS CountryOfOrigin
, isd.RowNumber
, p.PartId
FROM ImportStagingDetail isd
LEFT OUTER JOIN Part p ON (LTRIM(RTRIM(isd.PartNumber)) = p.PartNumber)
WHERE isd.ImportStagingId = @ImportStagingId;
PRINT 'part no =' + cast(@PartNumber as varchar(100))
OPEN NewPartCursor;
FETCH NEXT FROM NewPartCursor INTO @PartNumber, @FullDescription, @ShortDescription, @ShippingWeightLB,
@ShippingWeightKG, @ListPrice, @ListPriceText, @PriceLists, @CountryOfOrigin, @RowNumber, @PartId;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Part Number..='+@PartNumber;
IF (@TxnCount = 0)
BEGIN
BEGIN TRANSACTION ProcessImportDataTransaction;
END;
-- check if row already exists in the part table; if it doesn't, add it. if it does, update it if necessary.
IF (@PartId IS NULL)
BEGIN
-- create the part
INSERT INTO Part (PartNumber, InitialVersionId)
VALUES (@PartNumber, @VersionId);
SELECT @NewPartId = SCOPE_IDENTITY();
PRINT 'New part id ='+ cast(@NewPartId as varchar(100));
-- insert into part detail
INSERT INTO PartDetail (PartId, FullDescription, ShortDescription, ShippingWeightLB, ShippingWeightKG, ListPrice, ListPriceText, CountryOfOrigin)
VALUES (@NewPartId, @FullDescription, @ShortDescription, @ShippingWeightLB, @ShippingWeightKG, @ListPrice, @ListPriceText, @CountryOfOrigin);
SELECT @NewPartDetailId = SCOPE_IDENTITY();
INSERT INTO PartDetailVersion (PartDetailId, VersionId)
VALUES (@NewPartDetailId, @VersionId);
-- parse the price lists and add the part to the appropriate categories
SELECT @lidx = 1;
SELECT @idx = CHARINDEX(',', @PriceLists);
WHILE @idx > 0
BEGIN
SELECT @CheckCat = SUBSTRING(@PriceLists, @lidx, @idx-@lidx);
INSERT INTO PartCategory (PartId, CategoryId, VersionId)
SELECT @NewPartId
, c.CategoryId
, @VersionId
FROM Category c
WHERE c.CategoryString = @CheckCat;
SELECT @lidx = @idx+1;
SELECT @idx = CHARINDEX(',', @PriceLists, @lidx);
END;
END;
ELSE
BEGIN
-- part already exists
PRINT 'Partid already exits='+cast(@PartId as varchar(100));
-- check if the part detail is different any version
SELECT @MatchingPartDetailId = MAX(PartDetailId)
FROM PartDetail
WHERE PartId = @PartId
AND LTRIM(RTRIM(FullDescription)) = @FullDescription
AND LTRIM(RTRIM(ShortDescription)) = @ShortDescription
AND ISNULL(ShippingWeightLB, -55.00) = ISNULL(@ShippingWeightLB, -55.00)
AND ISNULL(ShippingWeightKG, -55.00) = ISNULL(@ShippingWeightKG, -55.00)
AND ISNULL(ListPrice, -55.00) = ISNULL(@ListPrice, -55.00)
AND LTRIM(RTRIM(ListPriceText)) = @ListPriceText
AND LTRIM(RTRIM(CountryOfOrigin)) = @CountryOfOrigin;
IF (@MatchingPartDetailId IS NULL)
BEGIN
-- insert into part detail
INSERT INTO PartDetail (PartId, FullDescription, ShortDescription, ShippingWeightLB, ShippingWeightKG, ListPrice, ListPriceText, CountryOfOrigin)
VALUES (@PartId, @FullDescription, @ShortDescription, @ShippingWeightLB, @ShippingWeightKG, @ListPrice, @ListPriceText, @CountryOfOrigin);
SELECT @NewPartDetailId = SCOPE_IDENTITY();
INSERT INTO PartDetailVersion (PartDetailId, VersionId)
VALUES (@NewPartDetailId, @VersionId);
END;
ELSE
BEGIN
-- part detail already exists, just need to create a new part detail version
INSERT INTO PartDetailVersion (PartDetailId, VersionId)
VALUES (@MatchingPartDetailId, @VersionId);
END;
-- parse the price lists and add the part to the appropriate categories
SELECT @lidx = 1;
SELECT @idx = CHARINDEX(',', @PriceLists);
WHILE @idx > 0
BEGIN
SELECT @CheckCat = SUBSTRING(@PriceLists, @lidx, @idx-@lidx);
INSERT INTO PartCategory (PartId, CategoryId, VersionId)
SELECT @PartId
, c.CategoryId
, @VersionId
FROM Category c
WHERE c.CategoryString = @CheckCat;
SELECT @lidx = @idx+1;
SELECT @idx = CHARINDEX(',', @PriceLists, @lidx);
END;
-- copy any part default, part document, part group/subgroup, part additional info that may already exist
-- what is the max version
SELECT @PriorVersionId = MAX(pc.VersionId)
FROM PartCategory pc
WHERE pc.PartId = @PartId
AND pc.VersionId != @VersionId;
IF (@PriorVersionId IS NOT NULL)
BEGIN
INSERT INTO PartAdditionalInfo (PartId, VersionId, AgentId, IsAgentFlag, IsContainerFlag, MinFillLB, MinFillKG, MaxFillLB, MaxFillKG, DefaultNozzle)
SELECT pai.PartId
, @VersionId
, pai.AgentId
, pai.IsAgentFlag
, pai.IsContainerFlag
, pai.MinFillLB
, pai.MinFillKG
, pai.MaxFillLB
, pai.MaxFillKG
, pai.DefaultNozzle
FROM PartAdditionalInfo pai
WHERE pai.PartId = @PartId
AND pai.VersionId = @PriorVersionId;
-- check to see if the part groups this would belong to already exist.
INSERT INTO PartGroup (CategoryId, VersionId, GroupName, Notes, DisplayOrder)
SELECT pg.CategoryId
, @VersionId
, pg.GroupName
, pg.Notes
, pg.DisplayOrder
FROM PartGroup pg INNER JOIN PartPartGroupLink ppgl ON pg.PartGroupId = ppgl.PartGroupId
WHERE ppgl.PartId = @PartId
AND pg.VersionId = @PriorVersionId
AND NOT EXISTS (SELECT *
FROM PartGroup pg0
WHERE pg0.CategoryId = pg.CategoryId
AND pg0.VersionId = @VersionId
AND pg0.GroupName = pg.GroupName);
INSERT INTO PartPartGroupLink (PartId, PartGroupId)
SELECT ppgl.PartId
, pg0.PartGroupId
FROM PartPartGroupLink ppgl
INNER JOIN PartGroup pg ON (ppgl.PartGroupId = pg.PartGroupId)
INNER JOIN PartGroup pg0 ON (pg.CategoryId = pg0.CategoryId AND
pg.GroupName = pg0.GroupName AND
pg0.VersionId = @VersionId)
WHERE ppgl.PartId = @PartId
AND pg.VersionId = @PriorVersionId
-- check to see if the part subgroups this would belong to already exist.
INSERT INTO PartSubgroup (SubgroupName, PartGroupId, Notes, DisplayOrder)
SELECT ps.SubgroupName
, pgx.PartGroupId
, ps.Notes
, ps.DisplayOrder
FROM PartSubgroup ps INNER JOIN PartPartSubgroupLink ppsl ON (ps.PartSubgroupId = ppsl.PartSubgroupId)
INNER JOIN PartGroup pg ON ps.PartGroupId = pg.PartGroupId
INNER JOIN PartGroup pgx ON (pg.CategoryId = pgx.CategoryId AND
pg.GroupName = pgx.GroupName AND
pgx.VersionId = @VersionId)
WHERE ppsl.PartId = @PartId
AND pg.VersionId = @PriorVersionId
AND NOT EXISTS (SELECT *
FROM PartSubgroup ps0
WHERE ps0.PartGroupId = pgx.PartGroupId
AND ps0.SubgroupName = ps.SubgroupName);
--INSERT INTO PartSubgroup (SubgroupName, PartGroupId, Notes)
-- SELECT ps.SubgroupName
-- , pgx.PartGroupId
-- , ps.Notes
-- FROM PartSubgroup ps INNER JOIN PartPartSubgroupLink ppsl ON (ps.PartSubgroupId = ppsl.PartSubgroupId)
-- INNER JOIN PartGroup pg ON ps.PartGroupId = pg.PartGroupId
-- INNER JOIN PartGroup pgx ON (pg.CategoryId = pgx.CategoryId AND
-- pg.GroupName = pgx.GroupName AND
-- pgx.VersionId = @VersionId)
-- WHERE ppsl.PartId = @PartId
-- AND pg.VersionId = @PriorVersionId
-- AND NOT EXISTS (SELECT *
-- FROM PartSubgroup ps0 INNER JOIN PartGroup pg0 ON ps0.PartGroupId = pg0.PartGroupId
-- WHERE pg0.CategoryId = pg.CategoryId
-- AND pg0.VersionId = @VersionId
-- AND pg0.GroupName = pg.GroupName
-- AND ps0.PartGroupId = ps.PartGroupId
-- AND ps0.SubgroupName = ps.SubgroupName);
INSERT INTO PartPartSubgroupLink (PartId, PartSubgroupId)
SELECT ppsl.PartId
, ps0.PartSubgroupId
FROM PartPartSubgroupLink ppsl
INNER JOIN PartSubgroup ps ON (ppsl.PartSubgroupId = ps.PartSubgroupId)
INNER JOIN PartGroup pg ON (ps.PartGroupId = pg.PartGroupId)
INNER JOIN PartGroup pg0 ON (pg.CategoryId = pg0.CategoryId AND
pg.GroupName = pg0.GroupName AND
pg0.VersionId = @VersionId)
INNER JOIN PartSubgroup ps0 ON (pg0.PartGroupId = ps0.PartGroupId AND
ps.SubgroupName = ps0.SubgroupName)
WHERE ppsl.PartId = @PartId
AND pg.VersionId = @PriorVersionId;
INSERT INTO PartDocument (PartId, BrandId, VersionId, DocumentName, PartDocumentFileId)
SELECT pd.PartId
, pd.BrandId
, @VersionId
, pd.DocumentName
, pd.PartDocumentFileId
FROM PartDocument pd
WHERE pd.PartId = @PartId
AND pd.VersionId = @PriorVersionId;
DECLARE @PDId INT, @NewPartDefaultId INT;
DECLARE PartDefaultCursor CURSOR FOR
SELECT pd.PartDefaultId
FROM PartDefault pd
WHERE pd.PartId = @PartId
AND pd.VersionId = @PriorVersionId;
OPEN PartDefaultCursor;
FETCH NEXT FROM PartDefaultCursor INTO @PDId;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'previous PartDefaultId=' + cast(@PDId as varchar(100)) + ' for version='+ cast(@VersionId as varchar(100));
INSERT INTO PartDefault (PartId, CategoryId, VersionId, DefaultPartId, DefaultQuantity, UseDefaultQuantityFlag, PerOrderFlag)
SELECT pd.PartId
, pd.CategoryId
, @VersionId
, pd.DefaultPartId
, pd.DefaultQuantity
, pd.UseDefaultQuantityFlag
, pd.PerOrderFlag
FROM PartDefault pd
WHERE pd.PartDefaultId = @PDId;
SELECT @NewPartDefaultId = SCOPE_IDENTITY();
PRINT 'New PartDefaultId=' + cast(@NewPartDefaultId as varchar(100)) ;
INSERT INTO PartDefaultDependency (PartDefaultId, SystemOptionId, ChoiceNumber)
SELECT @NewPartDefaultId
, pdd.SystemOptionId
, pdd.ChoiceNumber
FROM PartDefaultDependency pdd
WHERE pdd.PartDefaultId = @PDId;
FETCH NEXT FROM PartDefaultCursor INTO @PDId;
END;
CLOSE PartDefaultCursor;
DEALLOCATE PartDefaultCursor;
END;
END;
FETCH NEXT FROM NewPartCursor INTO @PartNumber, @FullDescription, @ShortDescription, @ShippingWeightLB,
@ShippingWeightKG, @ListPrice, @ListPriceText, @PriceLists, @CountryOfOrigin, @RowNumber, @PartId;
SELECT @TxnCount = @TxnCount + 1;
IF (@TxnCount > @TxnCountLimit)
BEGIN
SELECT @TxnCount = 0;
COMMIT TRANSACTION ProcessImportDataTransaction;
END;
END;
CLOSE NewPartCursor;
DEALLOCATE NewPartCursor;
COMMIT TRANSACTION ProcessImportDataTransaction;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorLine INT;
DECLARE @CursorStatus INT;
SELECT @CursorStatus = CURSOR_STATUS('global', 'NewPartCursor');
IF (@CursorStatus >= 0)
BEGIN
CLOSE NewPartCursor;
DEALLOCATE NewPartCursor;
END;
SELECT
@ErrorLine = ERROR_LINE(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
--ROLLBACK TRANSACTION ProcessImportDataTransaction;
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
END;
Submit an article or tip