Overflowed int column -- sql server programming

Roger Denison
2/26/2004 10:29:38 PM

Here is a copy of the SP:

/*
Purpose: Given a marriage tag code, return the original
barcode
Input params: Marriage tag code, type varchar(10)
Output params: Original bar code, type varchar(10)
Error params: Output of '9' indicates lookup failed
*/
CREATE PROCEDURE sp_MarriageLookup
@Tag varchar(10),
@Barcode varchar(10) output
AS
Declare @eventid int
Declare cur_Marriage cursor for
Select CurrentTag from tblMarriage
Where MarriageTag = @Tag
Open cur_Marriage
Fetch next from cur_Marriage into @Barcode
If (@@Fetch_Status <> 0)
Begin
/* Error log: Lookup failed */
Set @Barcode = 9
Set @eventid = 901
End
Else Begin
Fetch Next from cur_Marriage into @Barcode
If (@@Fetch_Status = 0)
Begin
/* Error log: More than one entry for the marriage
code */
Set @Barcode = 9
Set @eventid = 902
End
End
Close cur_Marriage
Deallocate cur_Marriage
If (@Barcode = 9)
Begin
Execute sp_LogError 1, @eventid, 0, @Tag
End

GO
[quoted text, click to view]

Piotr Hamrol
2/27/2004 7:50:19 AM

Maximum value for int (SQL Server) data type is 2147483647.
So, if you convert varchar value '2820103430' to int,
overflow error is normal.

In the secend case, when this stored procedure
work correctly on another server, check data type
of the column to which this sp convert varchar parameter

ph

[quoted text, click to view]

Piotr Hamrol
2/27/2004 8:11:07 AM

Compare the structure of tblMarriage table on both databases,
and check data type for MarriageTag column

ph

[quoted text, click to view]

Piotr Hamrol
2/27/2004 9:04:22 AM

Compare the structure of tblMarriage table on both databases,
and check data type for MarriageTag column

ph

Uzytkownik "Roger Denison" <anonymous@discussions.microsoft.com> napisal w
wiadomosci news:02eb01c3fcfb$1367dab0$a601280a@phx.gbl...
[quoted text, click to view]

Joe Celko
2/27/2004 9:49:32 AM

This sounds like a failure to design data correctly. Let's start with
the basics; did you reallllllly to put that "sp_" on the name? That is
a proprietary and special meaning. While it might be deliberate, it is
usually an error 99% of the time.

I know that the "tbl-" prefix is an error; while gay marriages are an
issue, no state has yet allowed people to marry furniture :) [Read
ISO-11179 for the right way to name data elements -- you name things for
what they are LOGICALLY in the data model, never for where or how they
are stored in the PHYSICAL database]

[quoted text, click to view]
nchar type variable so I don't understand the 'int column' overflow
issue. <<

You can easily overflow an integer with ten digits; look up the max
limits of the datatypes in BOL. Do not use INTEGER for this data
element.

But it looks like you left out a CHECK() constraint on at least one
table -- wish we had some DDL to look at, so we don't have to guess. I
am also willing to bet that the barcode is a fixed length column and
should be declared something like this

barcode CHAR(10) -- fixed length
DEFAULT '0000000000' -- assumption
NOT NULL -- assumption
CHECK (barcode LIKE
'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' -- requirement

While I guess it is possible, variable length barcodes would be a bitch
to check digit and I have never seen one before.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Overflowed int column