CODEWITHSUNDEEP
t-sqlsql-server-2005
Michael Rea
Michael Rea

Reputation: 31

Why is SQL function changing decimal places to all zeros?

Ok I have a number that is an nvarchar and has 2 decimal places. If I run it through a very simple function it changes all of the decimal places to zeros and I am not sure why or how to stop it.

Here is the basic function (actually part of a larger function, but this is what is causing my issue).

create FUNCTION [dbo].[z_test](@amt as nvarchar)
RETURNS decimal(18,4)
BEGIN 
declare @amt1 as decimal(18,4)
set @amt1=cast(@amt as decimal(18,4))
return @amt1
END

So if I run this SQL statement

select cast('3.48' as decimal(18,4)), dbo.z_test('3.48')

I would expect the columns returned to be the same. However what I get is 3.4800 3.0000

Any ideas on why this happens or how I can change it? This is in SQL Server 2005. Thanks in advance.

Upvotes: 2

Views: 838

Answers (2)

Khan
Khan

Reputation: 18152

Your input variable is being truncated.

According to MSDN: http://msdn.microsoft.com/en-us/library/ms186939.aspx

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

Give the NVARCHAR a size and your function will work as expected:

create FUNCTION [dbo].[z_test](@amt as nvarchar(10))
RETURNS decimal(18,4)
BEGIN 
declare @amt1 as decimal(18,4)
set @amt1=cast(@amt as decimal(18,4))
return @amt1
END

Upvotes: 3

Tim Lehner
Tim Lehner

Reputation: 15251

You should specify a length for nvarchar in your function parameters, otherwise it will assume a length of 1 in this case, truncating your input:

(@amt as nvarchar(10))

Upvotes: 3

Related Questions