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 the
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) 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,'gawrisch',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 = 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 get
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 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) 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,'gawrisch',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 = 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 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|||nice catch...I thought it had to be something like that but I tried set
ansi_nulls off with an alter on the procedure...that did not work and that
is why I thought it was some other problem...didn't occur to me to drop and
recreate
also nice call with the @.birthCountry IS NULL was not looking at it from
that perspective...will be much cleaner...ticks me off because that was a dah!
Thnx again
"Razvan Socol" wrote:
> 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
>|||Solved because of the ansi_nulls set to on upon creation of func...missed that
Thnx for help though
"Wei-Dong XU [MS]" wrote:
>
> 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.
>
>|||You are very welcome! Enjoy a nice weekend!
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.