Microsoft KB Archive/308826

= FIX: User Defined Function Returns Incorrect Result for SELECT Statement with DEFAULT Keyword That References a View =

Article ID: 308826

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q308826



BUG #: 353679 (SHILOH_BUGS)



SYMPTOMS
SQL Server 2000 returns incorrect results if you use the DEFAULT keyword with a user defined function (UDF) in a SELECT statement that references a view.



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack



WORKAROUND
To work around this problem, you can force SQL Server to pass the exact value into the SELECT statement instead of using the DEFAULT keyword. To do this, first query the function and store the default value in a temporary variable. Then, you can use the variable to query the UDF while referencing a view. For example, you can use these statements to query the UDF: declare @tmp sql_variant select @tmp = dbo.test_udf(default) select top 1 dbo.test_udf(@tmp) as view_retval from pubs..testauthors The SELECT statement returns the expected result.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.



MORE INFORMATION
To reproduce the problem, execute the following code on SQL Server 2000: Use pubs go CREATE VIEW testauthors AS SELECT * FROM authors GO create function test_udf (@i sql_variant = 'x') returns sql_variant as begin return( @i ) end go select top 1 dbo.test_udf( default ) as tbl_retval from pubs..authors select top 1 dbo.test_udf( default ) as view_retval from pubs..testauthors go drop function test_udf drop view testauthors go Note that the first SELECT statement returns the expected value of x; however, the second SELECT statement returns an incorrect value of 0.

Additional query words: wrong incorrect data results

Keywords: kbbug kbfix KB308826

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.