Ad-hoc SQL and inline function giving different results

Question

In MS SQL Server 2012 SP1, when I execute:

SELECT  rt.RoleId, ur.RoleName, app.ApplicationName
FROM    [TV_ClientPortal].[dbo].[UserRoleTie] rt
            JOIN [TV_ClientPortal].[dbo].[UserRoles] ur
                ON rt.RoleId = ur.RoleId
            JOIN [TV_ClientPortal].[dbo].[Applications] app
                ON app.ApplicationId = ur.ApplicationId
WHERE   rt.UserId = 255 AND
        ('SalesCRM' IS NULL OR app.ApplicationName = 'SalesCRM')

I get one row. Now I wrote an inline function thus:

CREATE FUNCTION dbo.Func_GetRolesForUser 
(   
    @UserId int,
    @AppName varchar
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT  rt.RoleId, ur.RoleName, app.ApplicationName
    FROM    [TV_ClientPortal].[dbo].[UserRoleTie] rt
                JOIN [TV_ClientPortal].[dbo].[UserRoles] ur
                    ON rt.RoleId = ur.RoleId
                JOIN [TV_ClientPortal].[dbo].[Applications] app
                    ON app.ApplicationId = ur.ApplicationId
    WHERE   rt.UserId = @UserId AND
            (@AppName IS NULL OR app.ApplicationName = @AppName)
)
GO

But when I execute

SELECT * FROM dbo.Func_GetRolesForUser(255, 'SalesCRM')

I get no results. Any idea why this might be the case? I thought about permission issues, but so far as I know, I have full permission to execute the function.

Answer 1

In SQL Server, never use varchar() without a length. Change the definition to something like:

CREATE FUNCTION dbo.Func_GetRolesForUser 
(   
    @UserId int,
    @AppName varchar(255)
)

And recreate the function.

In this context, the default length is 1, so the value is being passed in as 'S'.

Answer 2

What is the purpose of where clause "'SalesCRM' IS NULL"? Can you just rewrite as

(app.ApplicationName IS NULL OR app.ApplicationName = 'SalesCRM')

Therefore the filter in your table function code would be:

(app.ApplicationName IS NULL OR app.ApplicationName = @AppNam)