Tuesday, March 1, 2011

Data Type for SET ROWCOUNT

You might know SET ROWCOUNT is used to return exact number of records from a table.

You can set the row count,
SET ROWCOUNT 50

When you execute following statment,
SELECT * FROM dbo.RowCountTable

you will get only 50 records regardless of number of rows you have.

But what if you execute a query like that.

SET ROWCOUNT 2147500000

When you execute this, you will get following error.

Msg 1080, Level 15, State 1, Line 1
The integer value 2147500000 is out of range.


Mind you, maximum value for interger, 2,147,483,647 since 2147500000 is more than the maximum of the integer and row count is expecting a value of a interger.

So when you are passing a value to a ROW COUNT, it has to be a integer value.

Do not use something like below,

DECLARE @rccount bigint
SET ROWCOUNT @rccount

Though there won't be any issues, if you pass values less than maximum of integer, but make sure those parameters are integer.

No comments:

Post a Comment