Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

conversion of the varchar value overflowed an int column Expand / Collapse
Author
Message
Posted Monday, May 6, 2013 1:34 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:54 AM
Points: 1,921, Visits: 2,345
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/
Post #1449610
Posted Monday, May 6, 2013 2:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:54 AM
Points: 1,921, Visits: 2,345
Instead of RETURN I have added a SELECT @pSTN statement for the different different conditions and its working :)
Is it correct?



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1449627
Posted Monday, May 6, 2013 6:25 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:08 PM
Points: 4,573, Visits: 8,353
kapil_kk (5/6/2013)
Instead of RETURN I have added a SELECT @pSTN statement for the different different conditions and its working :)
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.
Post #1449687
Posted Monday, May 6, 2013 7:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:54 AM
Points: 1,921, Visits: 2,345
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 :)
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 :)



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1449704
Posted Monday, May 6, 2013 10:28 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:48 PM
Points: 20,734, Visits: 32,499
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 :)
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 :)


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.



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)
Post #1449787
Posted Monday, May 6, 2013 11:16 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:54 AM
Points: 1,921, Visits: 2,345
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 :)
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 :)


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/
Post #1449979
Posted Tuesday, May 7, 2013 1:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 6,782, Visits: 13,988
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
Post #1450009
Posted Tuesday, May 7, 2013 1:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:54 AM
Points: 1,921, Visits: 2,345
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/
Post #1450010
Posted Tuesday, May 7, 2013 1:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 6,782, Visits: 13,988
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
Post #1450014
Posted Tuesday, May 7, 2013 3:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 6,782, Visits: 13,988
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
Post #1450040
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse