Return value( Int vs Numeric) - 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

SQL Server Development (2000)

Return value( Int vs Numeric)

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-13 : 19:19:08

hello Everyone,
am working on a billing system. That has its credit column as a numeric value (8,2) precision and scale

Now, after a user must have submited a request, it bills the user based on available credits and returns a value to the calling app, which is an ASP page

However, it gives an error being a numeric value. I cant convert it to integer, due to the scale

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value '115.00' to a column of data type int.

how do i get around this ?
Afrika

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-13 : 21:11:23

afrika,

at what stage it is failing?
Is it when trying to insert data to a table or
trying to display the retrieved data?

Check whether the variable types in both sides r same.
[ I think in ASP data is passed as string data and in the server side, it has to be parsed to appropriate type]

I think the data is as varchar, but needs to be converted to decimal or float or whatever the appropriate type.
eg. U may be trying to assign a varchar type to a int type variable or trying to do a mathematical operation with a varchar type

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-14 : 04:23:37

>>at what stage it is failing?
In the ASP page, the SP completes its work and returns a numeric value

Which gives an error

variables are the same, except. The return value is an INT in the ASP page, and numeric in the SP,if i change it to numeric in the ASP page,it fails. And if i change to INT in the SP, i loose the precision/scale

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-14 : 07:37:08

So how r u going to hold a numeric (with decimals) by an integer type variable. Don't u have to change either of the sides to match with each other.

If u want to keep the precision, u can define the variable in ASP side as a "double" or "single" to hold the returned value (from DB).

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-14 : 09:21:07

well i might just have to convert to integer, if there is no other way

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-14 : 09:32:39

Can you return the value via an output parameter instead of a return value?

Be One with the Optimizer
TG

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-14 : 09:49:11

>>Can you return the value via an output parameter instead of a return value?

I thought there were the same thing ???

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-14 : 09:55:09

We may be mis-communicating:


create proc myProc
  @myoutputvar numeric(9,2) OUTPUT
as
set @myoutputvar = 110.25

--return value
 return -1
go

Be One with the Optimizer
TG

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-14 : 12:09:52

I think i better read up on return codes again

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-14 : 12:43:08

Sorry, that wasn't a very clear explanation. Here are three ways to get data from an SP in action:


create proc myProc
  @myoutputvar numeric(9,2) OUTPUT
as

declare @returnVal int

select	@myoutputvar = 110.25
	,@returnVal = 9999

select	'tgb' [username]

return @returnVal
go

----------------------------------------------
declare @out numeric(9,2)
	,@ret int

exec @ret = myProc @myoutputvar = @out output

print '
display output var value and return value'
select	@out [out]
	,@ret [ret]
----------------------------------------------
go

drop proc myProc

Be One with the Optimizer
TG

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-25 : 04:21:03

sorry this is coming late, but thanks TG

It works like a charm. And i can get both values and the recordset back

Afrika

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-25 : 05:19:55

Just one problem though.

How do i read it in an ASP page. Any advice? below is my asp code


set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = myconnection
Command1.CommandText = "dbo.myproc"
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 3, 4)
Command1.Parameters.Append Command1.CreateParameter("@myoutputvar ", 131, 2,18, out_put)
Command1.Execute()

Any Assistance on this ???

pootle_flump

1064 Posts

Posted - 2006-01-25 : 08:09:12

Command1.Parameters("@myoutputvar").Value
????

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-25 : 09:00:56

Oh silly me.

Thanks

by the way, welcome to SQLTeam

pootle_flump

1064 Posts

Posted - 2006-01-25 : 11:27:12