Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


conversion of the varchar value overflowed an int column


conversion of the varchar value overflowed an int column

Author
Message
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
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
               Wink
   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

END

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
Instead of RETURN I have added a SELECT @pSTN statement for the different different conditions and its working Smile
Is it correct?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5825 Visits: 11396
kapil_kk (5/6/2013)
Instead of RETURN I have added a SELECT @pSTN statement for the different different conditions and its working Smile
Is it correct?

Yes, it is correct.

In fact, "RETURN @pSTN " was incorrect.

In procedures RETURN_VALUE by default contains error code generated by procedure execution (0 if there is no error).
You may overturn the default value with some other code, but it's vedry not-recommended practice.

RETURN as you used it must be used in scalar functions only.
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
Sergiy (5/6/2013)
kapil_kk (5/6/2013)
Instead of RETURN I have added a SELECT @pSTN statement for the different different conditions and its working Smile
Is it correct?

Yes, it is correct.

In fact, "RETURN @pSTN " was incorrect.

In procedures RETURN_VALUE by default contains error code generated by procedure execution (0 if there is no error).
You may overturn the default value with some other code, but it's vedry not-recommended practice.

RETURN as you used it must be used in scalar functions only.

Thanks for the suggestion Smile

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24171 Visits: 37936
kapil_kk (5/6/2013)
Sergiy (5/6/2013)
kapil_kk (5/6/2013)
Instead of RETURN I have added a SELECT @pSTN statement for the different different conditions and its working Smile
Is it correct?

Yes, it is correct.

In fact, "RETURN @pSTN " was incorrect.

In procedures RETURN_VALUE by default contains error code generated by procedure execution (0 if there is no error).
You may overturn the default value with some other code, but it's vedry not-recommended practice.

RETURN as you used it must be used in scalar functions only.

Thanks for the suggestion Smile


Actually, looking at the declaration of your procedure the following is how you should be invoking your procedure.

Procedure declaration (partial):



ALTER PROCEDURE [dbo].[BS_GetSTNValue]
--DECLARE
   @pFromStore int = 1001
   ,@pToStore int = 1004
   ,@pSTN VARCHAR(20) = 0 output
...



Invoked:



declare @pSTNval varchar(20);

exec dbo.BS_GetSTNValue @pFromStore = ??, @pToStore = ??, @pSTN = @pSTNVal OUTPUT -- The ?? are what ever values for FromStore and ToStore you are passing to the procedure

select @pSTNVal -- display value returned from procedure




This will eliminate the SELECT @pSTN you added to your procedure.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
Lynn Pettis (5/6/2013)
kapil_kk (5/6/2013)
Sergiy (5/6/2013)
kapil_kk (5/6/2013)
Instead of RETURN I have added a SELECT @pSTN statement for the different different conditions and its working Smile
Is it correct?

Yes, it is correct.

In fact, "RETURN @pSTN " was incorrect.

In procedures RETURN_VALUE by default contains error code generated by procedure execution (0 if there is no error).
You may overturn the default value with some other code, but it's vedry not-recommended practice.

RETURN as you used it must be used in scalar functions only.

Thanks for the suggestion Smile


Actually, looking at the declaration of your procedure the following is how you should be invoking your procedure.

Procedure declaration (partial):



ALTER PROCEDURE [dbo].[BS_GetSTNValue]
--DECLARE
   @pFromStore int = 1001
   ,@pToStore int = 1004
   ,@pSTN VARCHAR(20) = 0 output
...



Invoked:



declare @pSTNval varchar(20);

exec dbo.BS_GetSTNValue @pFromStore = ??, @pToStore = ??, @pSTN = @pSTNVal OUTPUT -- The ?? are what ever values for FromStore and ToStore you are passing to the procedure

select @pSTNVal -- display value returned from procedure




This will eliminate the SELECT @pSTN you added to your procedure.

Hi Lynn,
I have made changes in this manner to the sp. Please let me know if it is right or not?

ALTER PROCEDURE [dbo].[BS_GetSTNValue]
--DECLARE
   @pFromStore int = 4001
   ,@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
               Wink
   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
            SELECT @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
         SELECT @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
         SELECT @pSTN      
   END
   --RETURN @pSTN

END

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19014
Four queries in your stored procedure use TOP (n) without ORDER BY. The results may be unpredictable.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
ChrisM@Work (5/7/2013)
Four queries in your stored procedure use TOP (n) without ORDER BY. The results may be unpredictable.

Hi Chris, I didn't get your point

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19014
TOP (n) without ORDER BY can return different results between executions of the same query. TOP 1 effectively means "give me a row from the set matching the search criteria, any row will do". That's very different to TOP 1 ... ORDER BY, where the first row of the ordered list would be returned.

Is this really what you want?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19014
I'm sure there's an easier (and safer) way of doing this.

ALTER PROCEDURE [dbo].[BS_GetSTNValue] 
--DECLARE
   @pFromStore int = 1001,
   @pToStore int = 1004,
   @pSTN VARCHAR(20) = 0 output
AS
--BEGIN

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

DECLARE @oldSTN VARCHAR(10)


------------------------------------------------------------------
-- If there isn't a row in GV_STNDetails
-- matching on @pFromStore and @pToStore
-- with a blank AWBNo (NULL or 0) then
-- create one, inserting only @pFromStore and @pToStore
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

-------------------------------------------------------------------
-- Get a random STNNO from GV_STNDetails
-- matching on @pFromStore and @pToStore
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

-------------------------------------------------------------------
-- Get a random AWBNo from GV_STNDetails
-- matching on @pFromStore and @pToStore
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

-------------------------------------------------------------------
-- Get the CountValue from GV_STNCountValues for @pFromStore
SET @count = (
SELECT gsv.CountValue
FROM GV_STNCountValues gsv
WHERE gsv.StoreCode = @pFromStore
)

IF @AWBNo = 0
BEGIN
   --IF CAST(@checkSTN as BIGINT) = 0
   IF @checkSTN = 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
      -- Get a random STNNO from GV_STNDetails
      -- matching on @pFromStore and @pToStore
      -- where AWBNo is blank (NULL or 0)
      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)
      
      -- assign the random STNNO to all rows in GV_STNDetails
      -- matching on @pFromStore and @pToStore
      -- where AWBNo is blank (NULL or 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
   -- Get a random STNNO from GV_STNDetails
   -- matching on @pFromStore and @pToStore
   -- where AWBNo has a value (not NULL or 0)   
   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
   Wink
   
   SET @pSTN = CONVERT(VARCHAR, REPLACE(STR(@pFromStore, 4), ' ', 0))
      + CONVERT(VARCHAR, REPLACE(STR(RIGHT(@oldSTN, 6) + 1,6),' ',0))
   PRINT @pSTN

   -- assign the random STNNO to all rows in GV_STNDetails
   -- matching on @pFromStore and @pToStore
   -- where AWBNo has a value (not NULL or 0)   
   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

--END



If you can correct the TOP(n) queries with ORDER BY, or better still MIN or MAX, it will clarify your intent. That in turn would make it much easier to rewrite this lot. It's well worth it.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search