问题描述:

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.

网友答案:

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))
网友答案:

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
相关阅读:
Top