Here is the interesting error one of my friend faced and it took me 15 minutes go over various code to identify the real issue. When we see a simple example in demonstration or a blog, we often think that SQL is a much simpler language and it looks easy. However, in reality it is often more complex than we think. I was dealing with the Stored Procedure which is which had 10000 lines of the code and there were many different views and functions used in it. The worst part was my friend can’t share his original code as it is owned by his company and I am just trying to help him externally. Well, here is the error he was facing.
Msg 1060, Level 15, State 1, Line 3
The number of rows provided for a TOP or FETCH clauses row count parameter must be an integer.
We worked together to identify the code which was generating the error. The code was as follows.
SET @param = 100 SELECT TOP (@param) * FROM Sales.SalesOrderDetail sod
Well, looking at the code everything looked normal. However, the error was still happening. It is common practice for developers to declare all the variables on the top of the page and later use them in the code. After few more attempts my friend was able to find the code where the variable @param was delcared. Now here is the simplified complete code.
DECLARE @param FLOAT SET @param = 100 SELECT TOP (@param) * FROM Sales.SalesOrderDetail sod
In this case the @param was declared as a float and that was the reason why the error was showing up. As error suggests the param passed in the TOP clause should be only Integer. In this case even though the value assigned to @param is INT the data type of the @param is FLOAT and that is why it is not accepted and it throws an error.
Here is the code which will fix the error.
DECLARE @param INT
SET @param = 100
SELECT TOP (@param) *
FROM Sales.SalesOrderDetail sod
After changing the datatype of the @param the error was resolved.
Reference:Â Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
