In TSQL, I need to do a cross-reference from our value for a given field, to the specified client's value. We have many clients and each client's encoding of values is different. The db is a CRM Dynamics 2011 db.

I set up a scalar-valued function like this:

[dbo].[fn_GetXRef] 
(@Guid uniqueidentifier, @LookupType nvarchar(20), 
 @OurValue nvarchar(20), @Parm4 nvarchar(20) = null,
 @Parm5 uniqueidentifier = null, @Parm6 nvarchar(1) = null,
 @Parm7 nvarchar(1) = null)

Parms 4, 5, 6 and 7 may be null; they are used for some cross-references but not others.

If I run execute the logic outside the function, it works. When I execute the function it returns NULL.

For example:

Select dbo.fn_getXRef('22BF20B1-55F1-E211-BF73-00155D062F00',
                            'Lookup Type 1', 'Our value', null,  null, null, '3')

It returns null but pulling the logic out of the function and running it as a separate query, and using the same input parameter values, returns the correct client-value.

What am I not seeing?

update: 12/11/13

Thanks all for trying to help. While researching I found some nifty code that looked more efficient than my own so I re-wrote the function using that technique and now it works. It uses OPTION (RECOMPILE):

SELECT @TheirValue = X.carriervalue
        FROM dbo.Filteredcrossreference X
        WHERE
        X.carrier = @CarrierId
        and X.lookuptype = @LookupType
        and X.ourvalue = @OurValue
        and (@Parm4 IS NULL OR (X.parm4 = @Parm4))
                    and (@Parm5 IS NULL OR (X.parm5 = @Parm5))
                    and (@Parm6 IS NULL OR (X.parm6 = @Parm6))

OPTION (RECOMPILE)