sql function throwing error when one of its parameters range is different

I have a sql function which i run using dynamic query.

Dynamic query is:

exec sp_executesql N'SELECT * 
                       FROM Client_Report_Consol_Summary_RELATED1(@p11,@p12,@p13,@p14)
                      WHERE (WEEKNUM = @p4) AND  (YEAR = @p5) 
                   ORDER BY ProductDescription,ISSUE_DESC,ConsigneeFullName',
N'@p11 uniqueidentifier,
@p12 nvarchar(4000),
@p13 n开发者_运维技巧varchar(4000),
@p14 nvarchar(4000),
@p4 decimal(1,0),
@p5 decimal(4,0)',
@p11='F312F49B-203E-4BBA-A74E-82EA356ED6D3',
@p12=NULL,
@p13=N'',
@p14=N'',
@p4=1,      <-- seems to be the issue
@p5=2011

When i run the above i get below error Arithmetic overflow error converting varchar to data type numeric.

but when i edit my dynamic query and update "@p4 decimal(1,0)" to "@p4 decimal(2,0)" it works fine.

Please can anyone throw some light on why is this happening.

Thanks Amit

Your WEEKNUM column coming out of your function is a string - and SQL server is trying to convert it to the numeric type so it can perform the comparison. So you either need to pass your param as a (n)varchar so that a string comparison occurs, or go with the fix you already have - make your numeric parameter large enough to accomodate any WEEKNUM value that will come out of this function.

I first created a dummy function that returned WEEKNUM as an int, but couldn't reproduce issue. The below function does demo the issue:

create function Client_Report_Consol_Summary_RELATED1 (
    @p11 uniqueidentifier,
@p12 nvarchar(4000),
@p13 nvarchar(4000),
@p14 nvarchar(4000)
)
returns table
as
    return (select '22' as WEEKNUM, 2011 as YEAR, 1 as ProductDescription,1 as ISSUE_DESC, 1 as ConsigneeFullName
    )
go

This error occurs when there is a data loss in your type conversion to numeric types. For example -

If your varchar field has a value "123" and you are trying to convert it to DECIMAL(1,0), it obviously cant hold complete 123 value. Hence it throws above error.