BUG: ROUND Function with NULL Length Returns TDS Error

ID: Q199105

The information in this article applies to:

Microsoft SQL Server version 7.0

BUG #: 53421 (SQLBUG_70)

SYMPTOMS

The Transact-SQL ROUND function may return a Tabular Data Stream (TDS) error if the second argument (length) is passed in as NULL.

WORKAROUND

To avoid the problem, check the second argument before passing it in to the ROUND function. If it is NULL, simply return NULL and do not use the ROUND function at all. You can use the ISNULL function to accomplish this.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

MORE INFORMATION

The syntax for the ROUND function is:


ROUND(numeric_expression, length [,function])

Where:

Numeric_expression is the number or expression to be rounded.

Length is the desired precision.

Function is either 0 or nonzero. The default (0) means to round; nonzero means to truncate.

If the length parameter is NULL and it is supplied either from a table column (as a parameter) or from a result set, the ROUND function results in one of the following TDS errors:

DB-Library: Possible network error: Bad token from SQL Server: Datastream processing out of sync.
Net-Library error 0: (null)
DB-Library: DBPROCESS is dead or not enabled.

-or-

[Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server.

For example, following query demonstrates this behavior:


Create table testtable (col1 int not null, col2 int null)
go
insert into testtable values(1,NULL)
go
select ROUND(col1,col2)from testtable
go
declare  @param int
select @param = NULL
select ROUND(1,@param)
go
select ROUND(1,(select NULL))
go

However, the if the NULL length is passed directly as:


select ROUND(1,NULL)

SQL Server returns error message 8116:

Argument data type void type is invalid for argument 2 of round function.

This is true for sp_dbcmptlevel 65 as well.

In SQL Server 6.5, such a query returns NULL.

Additional query words: err msg


Keywords          : SSrvTran_SQL kbbug7.00 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbbug

Last Reviewed: February 5, 1999