storedProcedure numeric parameter becomes int

Question

storedProcedure numeric parameter becomes int

russ lunn

Old Hand

Points: 387
More actions
October 26, 2007 at 4:10 am

#176602

HI,
i have a table with some numeric columns, and a stored procedure to do inserts
when i pass the string to exec it is a number (18.50) when it is inserted
it is an int that has been rounded down, if i try to specify p,s on the parameters
of the sp it gives an error, so how do i get round this? should i use varchar and
hope sqlserver does the right thing for me?
by the way the software that is using the procedure isnt able to create parameters
it just creates a string and runs it, so i can use .net type parameter creation
Any ideas, im confused by it!
Russ

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Russ

I'm not clear exactly what you're trying to do - what is the T-SQL that you're running? It may also help if you give us the table and SP definitions.

Thanks

John

Grant Fritchey SSC Guru Points: 398707 More actions · Answer 2

Unless it's impossible for some external reason, your stored procedure's parameters should always be of the data type that you're attempting to manipulate within the database. So, instead of a varchar, you should define your parameter as a decimal:

@Parm decimal(8,2)

But if you can't, then instead of relying on SQL Server to "get it right" you should use the CAST statement within your procedure to fix the data yourself.

CAST(@Parm AS decimal(8,2))

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

russ lunn Old Hand Points: 387 More actions · Answer 3

sorry to waste everyones time.

it was late and i hadnt had enough coffee.

i couldnt get @weight numeric(6.2) to work as an input parameter

because it needs a comma 6,2 not a dp 6.2

DOH.

Russ