SQL scalar function returns unexpected result

Question

I can't figure out why these 2 things return different results.

This SQL returns the letter P:

SELECT PROVIDERS.PROVIDERNBR, CONTRACTS.PROVIDERPARSTATUS
FROM
   dwpresentation_prod.dbo.PROVIDERS
   INNER JOIN dwpresentation_prod.dbo.CONTRACTS
      ON PROVIDERS.PROVIDERNBR = CONTRACTS.PROVIDERNBR
WHERE
   PROVIDERS.PROVIDERNBR = 'P18292'
   AND CONTRACTS.TERMDATE = 0;

But this returns a zero length string:

SELECT dbo.PENDS_BAA_ParProvider('P18292');

Here is the function:

ALTER FUNCTION [dbo].[PENDS_BAA_ParProvider](@ProviderNumber varchar)
RETURNS varchar(15)
AS
BEGIN
   DECLARE @result varchar (15);
   SET @result = IsNull((
      SELECT
         CASE 
         WHEN PROVIDERPARSTATUS IS NULL THEN ''
         WHEN PROVIDERPARSTATUS = 'P' THEN 'P'
         ELSE '' END
      FROM
         dwpresentation_prod.dbo.Providers PROVIDERS
         INNER JOIN dwPresentation_Prod.dbo.Contracts CONTRACTS
            ON PROVIDERS.ProviderNbr = CONTRACTS.ProviderNbr
      WHERE
         PROVIDERS.ProviderNbr = @ProviderNumber
         AND CONTRACTS.TermDate = 0
   ), '');

   RETURN @result;
END;

Without soliloquies on why am I using a function rather than an SQL statement (the answer has mostly to do with experimentation and learning), I'm wondering WHY the function produces a zero length string.

EDIT My current function now looks like this, but STILL returns a ZLS, as opposed to P:

Current function - STILL returns ZLS

ALTER FUNCTION [dbo].[PENDS_BAA_ParProvider](@ProviderNumber varchar)

RETURNS varchar(15) AS BEGIN

Declare @result varchar (15)

set @result=isnull((SELECT CASE WHEN PROVIDERPARSTATUS Is Null THEN '' WHEN PROVIDERPARSTATUS='P' THEN 'P' ELSE '' END

            FROM dwpresentation_prod.dbo.Providers PROVIDERS
                INNER JOIN dwPresentation_Prod.dbo.Contracts CONTRACTS
            ON PROVIDERS.ProviderNbr = CONTRACTS.ProviderNbr

            WHERE (PROVIDERS.ProviderNbr=@ProviderNumber AND CONTRACTS.TermDate=0)),'')

Return @result END GO

I don't think the top query returns P. I think it returns 6. — Dan Bracuk
The answer has to be that the PROVIDERPARSTATUS is not equal to P for ProviderNbr = 'P18292'. What if it's equal to another character that only looks like a P? — ErikE
@DanBracuk He means the second column of the first SQL statement, not the result of the Len. — ErikE

Gordon Linoff Gordon Linoff · Accepted Answer · 2014-09-03T01:39:28

Your problem is in the function definition. This:

ALTER FUNCTION [dbo].[PENDS_BAA_ParProvider](@ProviderNumber varchar)

is equivalent to:

ALTER FUNCTION [dbo].[PENDS_BAA_ParProvider](@ProviderNumber varchar(1))

In SQL Server, always include a length with character types:

ALTER FUNCTION [dbo].[PENDS_BAA_ParProvider](@ProviderNumber varchar(255))

SQL scalar function returns unexpected result

1 Answers