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

Back to the top

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.

Back to the top

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

Back to the top

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: -or- 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: This is true for sp_dbcmptlevel 65 as well.

In SQL Server 6.5, such a query returns NULL.

Back to the top