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)
declare @amt1 as decimal(18,4)
set @amt1=cast(@amt as decimal(18,4))
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
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.
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