locked
Conversion of the varchar value overflowed an int column RRS feed

  • Question

  • I have a stored procedure :
    In this prcedure I am returning @pSTN value and used in another sp as output. @pSTn value is generating on basis of From Store and suppose if my from store is 1001 thn @pstn value like '1001000111' is will retun no error but when my @fromstore variable is > 2 i.e. 3001 or 400 it will start giving me error 'The conversion of the varchar value '4001000012' overflowed an int column'.
    To prevent that i used CAST in the RETURN statment of the stored procedure but then it giving me error:
    'Arithmetic overflow error converting expression to data type int.'

    How to solve this?

    ALTER PROCEDURE [dbo].[BS_GetSTNValue] 
    --DECLARE
    @pFromStore int = 1001
    ,@pToStore int = 1004
    ,@pSTN VARCHAR(20) = 0 output
    AS
    BEGIN
    IF NOT EXISTS (
    SELECT 1 
    FROM GV_STNDetails gs 
    WHERE gs.From_StoreCode = @pFromStore 
    AND gs.TO_StoreCode = @pToStore AND ISNULL(gs.AWBNo,0) = 0
    )
    BEGIN
    INSERT INTO GV_STNDetails (From_StoreCode, TO_StoreCode)
    VALUES (@pFromStore,@pToStore)
    END

    DECLARE @count INT = 0
    ,@AWBNo VARCHAR(10) = 0,
    @checkSTN varchar(10)

    SET @checkSTN = (
    SELECT TOP 1 ISNULL(gs.STNNO, 0)
    FROM GV_STNDetails gs
    WHERE gs.From_StoreCode = @pFromStore
    AND gs.TO_StoreCode = @pToStore
    )
    PRINT @checkSTN

    SET @AWBNo = (
    SELECT TOP 1 ISNULL(gs.AWBNo, 0)
    FROM GV_STNDetails gs
    WHERE gs.From_StoreCode = @pFromStore
    AND gs.TO_StoreCode = @pToStore
    )
    PRINT @AWBNo
    SET @count = (
    SELECT gsv.CountValue
    FROM GV_STNCountValues gsv
    WHERE gsv.StoreCode = @pFromStore
    )

    IF @AWBNo = 0
    BEGIN
    IF CAST(@checkSTN as BIGINT) = 0 
    BEGIN
    SET @pSTN = CONVERT(VARCHAR, REPLACE(STR(@pFromStore, 4), ' ', 0)) + CONVERT(VARCHAR, REPLACE(STR(@count + 1, 6), ' ', '0'))
    PRINT 'WHEn STN & AWB is 0 ' + @pSTN

    UPDATE GV_STNDetails
    SET STNNO = @pSTN
    WHERE From_StoreCode = @pFromStore 
    AND TO_StoreCode = @pToStore
    AND ISNULL(AWBNo,0) = 0

    UPDATE GV_STNCountValues
    SET CountValue = CountValue + 1
    WHERE StoreCode = @pFromStore
    END
    ELSE
    BEGIN
    SET @pSTN = (SELECT TOP 1 gs.STNNO
    FROM GV_STNDetails gs
    WHERE gs.From_StoreCode = @pFromStore
    AND gs.TO_StoreCode = @pToStore AND ISNULL(AWBNo,0) = 0)
    UPDATE GV_STNDetails
    SET STNNO = @pSTN
    WHERE From_StoreCode = @pFromStore AND TO_StoreCode = @pToStore AND ISNULL(AWBNo,0) = 0
    PRINT 'same ' + @pstn
    END
    END
    ELSE
    BEGIN
    DECLARE @oldSTN VARCHAR(10)

    SET @oldSTN = (
    SELECT TOP 1 gs.STNNO
    FROM GV_STNDetails gs
    WHERE gs.From_StoreCode = @pFromStore
    AND gs.TO_StoreCode = @pToStore AND ISNULL(gs.AWBNo,0) <> 0
    )
    SET @pSTN = CONVERT(VARCHAR, REPLACE(STR(@pFromStore, 4), ' ', 0)) + CONVERT(VARCHAR, REPLACE(STR(RIGHT(@oldSTN, 6) + 1,6),' ',0))
    PRINT @pSTN 

    UPDATE GV_STNDetails
    SET STNNO = @pSTN
    WHERE From_StoreCode = @pFromStore AND TO_StoreCode = @pToStore AND ISNULL(AWBNo,0) = 0
    PRINT 'awb ' + @pSTN
    END
    RETURN @pSTN 

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Monday, May 6, 2013 8:45 AM

Answers

All replies

  • >it will start giving me error 'The conversion of the varchar value '4001000012' overflowed an int column'.

    Use BIGINT:

    SELECT CONVERT(BIGINT,'4001000012');

    INT range: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Monday, May 6, 2013 8:51 AM
  • INT datatype size is 4 byte and BIGINT is 8 byte. Why don't you try for BIGINT ?

    Regards, RSingh

    • Proposed as answer by Naomi N Tuesday, May 7, 2013 2:19 AM
    • Marked as answer by Allen Li - MSFT Wednesday, May 15, 2013 8:47 AM
    Monday, May 6, 2013 8:52 AM
  • As said in other replies , BIGINT would be a good option.

    With INT overflow , i remember the post on "Do Overflow occurs on BIGINT also", have a look it this post

    http://beyondrelational.com/modules/2/blogs/682/posts/15470/bigint-upper-limit-overflow-sql-server.aspx


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, May 7, 2013 6:08 AM
  • The RETURN value of a stored procedure is an int. This means you cannot return a value higher than approximately 2 billion.

    Using a bigint instead of an int will solve your conversion problem, but you will have to change your communication mechanism. You could use an output parameter (parameter of the stored procedure declare as OUTPUT), or you could SELECT your data, and call your stored procedure with INSERT INTO ... EXEC my_stored_procedure.

    My final piece of advice: is @pSTN really supposed to represent a number? Because if it is just a code or ID, I wouldn't convert, I would just keep it as varchar or char.


    Gert-Jan

    Wednesday, May 8, 2013 8:45 PM
  • To add to what Gert-Jan said, get a copy of THINKING IN SETS and SQL PROGRAMMING STYLE read the parts about DDL , scales and encoding schemes. Identifiers are not used for math, so they are string data types (with check digits and/or regular expressions to validate them).  If they are made of digits only, then they are called "tag numbers" in the literature. 

    Have you ever read any book on data modelling? 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, May 9, 2013 1:33 AM