Click here to Skip to main content
14,640,670 members
Rate this:
Please Sign up or sign in to vote.
See more:
this is my stored procedure here I am facing Exception of Invalid cast, kindly suggest...


USE [bomgen_UTC_PROD]
GO
/****** Object:  StoredProcedure [bomgen_Admin].[utc_NewProcessImportData]    Script Date: 03/30/2015 01:34:13 ******/
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;
Posted
Comments
CHill60 6-Apr-15 10:32am
   
That is a lot of code. Where is the error raised?
Meer Wajeed Ali 7-Apr-15 0:20am
   
Actually this is written by my ex colleague, so it is hard me to solve where I have debug.
ZurdoDev 6-Apr-15 10:33am
   
You need to debug the code. Do you understand what the error means? If you do, you'll be able to narrow down where the problem could be.
   
In what line?
—SA

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

The problem is with the schema of your table ImportStagingDetail
The columns
- ShippingWeightLB
- ShippingWeightKG
- ListPriceText

Are not numeric - in fact they appear to be varchar columns holding data that can include commas.

Always use the appropriate data type for storing data: Dates should be in Date or DateTime column types, numeric data should be stored in a numeric column type; and do not concern yourself with how data will "look" until you actually need to display it.

If you examine your data you will probably find that at least one of the values in at least one of the columns I listed does not look like a decimal(10,2) or money.

If you are still having problems after fixing the schema, try reducing the code down to the basics so you can see the wood for the trees. And remove commented out code before you post
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100