Precision invalid: inserting decimal via SP

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)

Precision invalid: inserting decimal via SP

Author

Topic

SquareEye
Starting Member

5 Posts

Posted - 2008-04-03 : 08:26:46

Hello

I'm feeling my way through stored procedures for the first time. I'd like to use one to insert a decimal value (e.g. 10.99) into a table, which has a field set to datatype 'decimal' with precision 8 and scale 2.

If a setup a stored procedure like this:

CREATE PROCEDURE procName
@Amount decimal(8,2)
AS
INSERT INTO TableName (FieldName) VALUES (@Amount)

Then I get the error "The precision is invalid." It works however if I do this instead:

CREATE PROCEDURE procName
@Amount nvarchar(8)
AS
INSERT INTO TableName (FieldName) VALUES (CONVERT(decimal(8,2),@Amount))

I'd like to tidy it up and have it treating the number as a number though - could anyone advise where I'm going wrong?

Many thanks in advance

Square

SquareEye
Starting Member

5 Posts

Posted - 2008-04-03 : 08:29:31

Oh and P.S. The bit of ASP/VBScript to go with it is (if trying to insert as decimal):

set Command001 = Server.CreateObject("ADODB.Command")
Command001.ActiveConnection = Database_String_Here
Command001.CommandText = "procName"
Command001.Parameters.Append Command001.CreateParameter("@RETURN_VALUE", 3, 4)
Command001.Parameters.Append Command001.CreateParameter("@Amount", 14, 1,8,Command001__PaymentRate)
Command001.CommandType = 4
Command001.CommandTimeout = 0
Command001.Prepared = true
Command001.Execute()

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-03 : 08:40:35

Are you using Double as parameter data type from VB?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SquareEye
Starting Member

5 Posts

Posted - 2008-04-03 : 08:58:44

Hi Harsh - not sure I understand the question I'm afraid - I'm a real novice and much of the code above comes straight from Dreamweaver...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-03 : 09:14:00

I am talking about this line:

Command001.Parameters.Append Command001.CreateParameter("@Amount", 14, 1,8,Command001__PaymentRate)

can you change it to this:

Command001.Parameters.Append Command001.CreateParameter("@Amount", adDecimal, adParamInput, 8,Command001__PaymentRate)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources