SP Decimal problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SP Decimal problem

Author

Topic

elomon
Starting Member

37 Posts

Posted - 2003-03-18 : 11:40:17

Have a table where the PK is a decimal length 13 (not my choice).
If I do this:
Select * from tblAddy where addid=50056003999885788178534
Everything works ok

But then I created a sp to return the info:
CREATE procedure spu_Addy_Detail
@addid decimal
As
Select * from
tblAddy
where
addid=@addid

When I execute spu_Addy_Detail 50056003999885788178534

I get the error:
Error converting data type numeric to decimal

I re-confirmed the addid is, in fact, decimal.
Any ideas appreciated. Thanks

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-03-18 : 12:00:16

Believe the decimal type needs a declaration of (precision,and scale)

CREATE procedure spu_Addy_Detail
(
@addid decimal(16,2)
)
As

....
don't know why QA dosen't let you know about it,
must default to (1,0)

Voted best SQL forum nickname...."Tutorial-D"

elomon
Starting Member

37 Posts

Posted - 2003-03-18 : 12:01:55

That did the trick perfectly.
Thank you very much.

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-18 : 12:04:45

Well, how about that it's not decimal and it's not 13 digits. Also, decimal deals with precision Declare @x(13,2).

Sheepishly Editing My response:

[WRONG]
Also, your "Number" is larger than the biggest datatype for numerics (I think):

BOL:

bigint

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).

Which is:

1234567890123456789
-------------------
9223372036854775807

You have:

12345678901234567890123
-----------------------
50056003999885788178534

Why not use varchar instead?
[/WRONG]

Brett

8-)

Edited by - x002548 on 03/18/2003 12:10:27

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources