2005 inline table function produce incorrect resultset than 2000

OK...this all works in 2000.
I have wondered why return results were different in an inline table
function (run on the same set of data) in 2005 than 2000. The function is th
e
same...a parametized function with a no join select on a table. The function
returns incorrect results yet if I run the sql statement that is in the
function as a stand alone in the server manager studio...it returns the
correct results. This problem does not exist in sql server 2000. Here is the
inline table function.
CREATE FUNCTION fn_UsrSearchPersontest ( @.firstName uddtFirstName=NULL,
@.middleName uddtMiddleName=NULL,
@.lastName uddtLastName=NULL, @.socialSecurity uddtSocialSecurity = NULL,
@.startDOB datetime = NULL, @.endDOB datetime = NULL,
@.birthCounty uddtBirthCounty=NULL, @.birthCountry uddtBirthCountry=NULL,
@.birthState uddtBirthState=NULL)
RETURNS TABLE
AS
RETURN
(
SELECT Id as PersonId FROM Persons
WHERE FirstName LIKE ISNULL(@.firstName, FirstName) AND (MiddleName LIKE
ISNULL(@.middleName, MiddleName) OR MiddleName = @.middleName)
AND LastName LIKE ISNULL(@.lastName, LastName) AND (SocialSecurity LIKE
ISNULL(@.socialSecurity, SocialSecurity) OR SocialSecurity = @.socialSecurity)
AND (BirthCounty LIKE ISNULL(@.birthCounty, BirthCounty) OR BirthCounty
= @.birthCounty) AND (BirthCountry LIKE ISNULL(@.birthCountry, BirthCountry) O
R
BirthCountry = @.birthCountry)
AND (BirthState LIKE ISNULL(@.birthState, BirthState) OR BirthState =
@.birthState)
)
running this statement:
set ansi_nulls off
SELECT personId FROM
fn_usrsearchpersontest(null,null,'gawris
ch',null,null,null,null,null,null)
does not return the right set if some of the records have nulls in the
corresponding fields addressed in the table function that has 'OR' as part o
f
the condition (i.e. social security)
Yet if I run this in studio it return the correct number of results
set ansi_nulls off
SELECT Id as PersonId FROM Persons
WHERE FirstName LIKE ISNULL(null, FirstName) AND (MiddleName LIKE
ISNULL(null, MiddleName) OR MiddleName = null)
AND LastName LIKE ISNULL('funke', LastName) AND (SocialSecurity LIKE
ISNULL(null, SocialSecurity) OR SocialSecurity = null)
AND (BirthCounty LIKE ISNULL(null, BirthCounty) OR BirthCounty = null)
AND (BirthCountry LIKE ISNULL(null, BirthCountry) OR BirthCountry = null)
AND (BirthState LIKE ISNULL(null, BirthState) OR BirthState = null)
thoughts? Like I said if this is run in sql server 2000 the results are the
same in either running the function or sql statement as above...I need to ge
t
this resolved for a migrationthe parameter in the second sql statement 'funke' should be
'gawrisch'...otherwise it wouldn't be the same result...sorry...bad
proofiing
"bLad3" wrote:

> OK...this all works in 2000.
> I have wondered why return results were different in an inline table
> function (run on the same set of data) in 2005 than 2000. The function is
the
> same...a parametized function with a no join select on a table. The functi
on
> returns incorrect results yet if I run the sql statement that is in the
> function as a stand alone in the server manager studio...it returns the
> correct results. This problem does not exist in sql server 2000. Here is t
he
> inline table function.
> CREATE FUNCTION fn_UsrSearchPersontest ( @.firstName uddtFirstName=NULL,
> @.middleName uddtMiddleName=NULL,
> @.lastName uddtLastName=NULL, @.socialSecurity uddtSocialSecurity = NULL,
> @.startDOB datetime = NULL, @.endDOB datetime = NULL,
> @.birthCounty uddtBirthCounty=NULL, @.birthCountry uddtBirthCountry=NULL,
> @.birthState uddtBirthState=NULL)
> RETURNS TABLE
> AS
> RETURN
> (
> SELECT Id as PersonId FROM Persons
> WHERE FirstName LIKE ISNULL(@.firstName, FirstName) AND (MiddleName LIK
E
> ISNULL(@.middleName, MiddleName) OR MiddleName = @.middleName)
> AND LastName LIKE ISNULL(@.lastName, LastName) AND (SocialSecurity LI
KE
> ISNULL(@.socialSecurity, SocialSecurity) OR SocialSecurity = @.socialSecurit
y)
> AND (BirthCounty LIKE ISNULL(@.birthCounty, BirthCounty) OR BirthCoun
ty
> = @.birthCounty) AND (BirthCountry LIKE ISNULL(@.birthCountry, BirthCountry)
OR
> BirthCountry = @.birthCountry)
> AND (BirthState LIKE ISNULL(@.birthState, BirthState) OR BirthState =
> @.birthState)
> )
> running this statement:
> set ansi_nulls off
> SELECT personId FROM
> fn_usrsearchpersontest(null,null,'gawris
ch',null,null,null,null,null,null)
> does not return the right set if some of the records have nulls in the
> corresponding fields addressed in the table function that has 'OR' as part
of
> the condition (i.e. social security)
> Yet if I run this in studio it return the correct number of results
> set ansi_nulls off
> SELECT Id as PersonId FROM Persons
> WHERE FirstName LIKE ISNULL(null, FirstName) AND (MiddleName LIKE
> ISNULL(null, MiddleName) OR MiddleName = null)
> AND LastName LIKE ISNULL('funke', LastName) AND (SocialSecurity LIKE
> ISNULL(null, SocialSecurity) OR SocialSecurity = null)
> AND (BirthCounty LIKE ISNULL(null, BirthCounty) OR BirthCounty = nul
l)
> AND (BirthCountry LIKE ISNULL(null, BirthCountry) OR BirthCountry = null)
> AND (BirthState LIKE ISNULL(null, BirthState) OR BirthState = null)
> thoughts? Like I said if this is run in sql server 2000 the results are th
e
> same in either running the function or sql statement as above...I need to
get
> this resolved for a migration|||Hi Blad,
Welcome to use MSDN Managed Newsgroup!
From your description, my understanding of this issue is: you use Inline
table to query some data under SQL Server 2005, but it returns the
different result with that the sql statement executed in Management Studio.
And also the same SQL statement return the same results in SQL Server 2000.
If I have misunderstood your concern, please feel free to point it out.
Since you defined this function on a specific database and you use
User-defined Data type in your table, I can not re-pro it on my own
environment. So for narrowing down the question, would you like to give me
some more information?
1. Which edition of SQL Server 2005 do you use?
2. What is the difference between the results of those 2 method? Does
the Inline table return all the record with NULL value in those
corresponding column?
3. Do you have any other function to query the table? If so, does this
issue happen on those function?
4. If you create a simple function use Inline table ( with less
criteria ) to query the table, does this issue happen?
If there are more information on the issue, please feel free to let us
know. Have a great day!
Best Regards,
Wei-Dong XU
Microsoft Support
----
This posting is provided "AS IS" with no warranties, and confers no rights.
----
It is my pleasure to be of any assistance.|||Here is a quote from Books Online:
For stored procedures, SQL Server uses the SET ANSI_NULLS setting
value from the initial creation time of the stored procedure.
Whenever the stored procedure is subsequently executed,
the setting of SET ANSI_NULLS is restored to its originally
used value and takes effect. When invoked inside a stored
procedure, the setting of SET ANSI_NULLS is not changed.
In this aspect, views and functions are also treated like stored
procedures. I guess you have created the function when SET ANSI_NULLS
was ON, so whenever you execute the function, it considers this
setting, regardless of the current state of SET ANSI_NULLS when the
function is invoked.
You can re-create the function with SET ANSI_NULLS OFF and it should
behave as you expect. However, I would not use SET ANSI_NULLS OFF and I
would modify the function to use conditions like this:
[...]
AND (BirthCountry LIKE @.birthCountry
OR BirthCountry = @.birthCountry
OR @.birthCountry IS NULL)
[...]
Razvan