SQL Clone
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
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3091 Visits: 2766
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

END

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

Group: General Forum Members
Points: 3091 Visits: 2766
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10350 Visits: 11961
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
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3091 Visits: 2766
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-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39374 Visits: 38549
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
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3091 Visits: 2766
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
)
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16254 Visits: 19551
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
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3091 Visits: 2766
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16254 Visits: 19551
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16254 Visits: 19551
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
)

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