SQL SERVER – Fix – Error: 1060 The number of rows provided for a TOP or FETCH clauses row count parameter must be an integer

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 (http://blog.sqlauthority.com)

10 thoughts on “SQL SERVER – Fix – Error: 1060 The number of rows provided for a TOP or FETCH clauses row count parameter must be an integer

  1. you could have avoided searching 10000 lines of code with cast or convert:

    DECLARE @param FLOAT
    SET @param = 5
    — with cast
    SELECT TOP (CAST ( @param AS int)) * FROM client;
    — convert
    SELECT TOP (CONVERT ( int , @param )) * FROM client;

    Like

  2. hello pinal and madhivana sir

    sir i have one question related to database
    i have one table like that
    sr.no name in time out time late time
    1 happy 9.45 18.00 15 min
    2 navneet 9.30 19.00
    3 vicky 10.15 20.00 1.15 hours

    i want to show that if one person has come 9.45 then 15 min come to the late time column. means no late time 9.30 time plz suggest me
    means in time 9.30 if person has come after that 9.30 then late time has come to the late time column plz suggest me
    happy
    thanks for advance

    Like

    • –I think that a view might meet your needs
      create table login_table (id int, name char(10), time_in time(0),time_out time(0));
      insert into login_table values
      (1,’happy’,’9:45:00′,’18:00:00′),
      (2,’navmeet’,’9:30:00′,’19:00:00′),
      (3,’vicky’,’10:15:00′,’20:00:00′);
      ——————–
      SELECT * from login_table;

      –CREATE view resultset as
      select *, datediff(minute,cast(‘janv 1 1900 9:30AM’ as datetime),cast(time_in as datetime))as late FROM login_table;
      –SELECT * from resultset

      DROP table login_table;

      Like

  3. hello sir i use this quey error has come

    Msg 1001, Level 15, State 1, Line 5
    Line 5: Length or precision specification 0 is invalid.

    but sir i want to late time come in l_time column

    ok sir

    Like

  4. /*
    if time(0) doesn’t work try time(1)
    if you want to change the name of the output column you can change it
    */
    /*When i recopied my code from the site the apostrophes had all been corrupted – has anyone else had this problem?*/
    CREATE TABLE login_table (id INT, name CHAR(10), time_in TIME(1),time_out TIME(1));
    INSERT INTO login_table VALUES
    (1,’happy’,’9:45:00′,’18:00:00′),
    (2,’navmeet’,’9:30:00′,’19:00:00′),
    (3,’vicky’,’10:15:00′,’20:00:00′);
    /*===============*/
    SELECT * FROM login_table;

    /* as create view has to be the first instruction of a lot I don’t use it in the example
    CREATE view resultset AS
    */
    SELECT
    *,
    datediff(minute,cast(‘janv 1 1900 9:30AM’ AS datetime),cast(time_in AS datetime))AS l_time
    FROM login_table;

    DROP TABLE login_table;

    Like

  5. CREATE TABLE login_table
    (id INT,
    oname CHAR(10),
    time_in TIME(1),
    time_out TIME(1)
    );

    give me error

    Msg 2715, Level 16, State 7, Line 1
    Column, parameter, or variable #3: Cannot find data type TIME.

    plz sir me reply fast

    Like

  6. hello sir i am using sql server 2005 express edition i think time data type use in sql server 2008. i am waiting your response

    happy

    Like

  7. hello sir i have create table with the help of date time but output is different

    CREATE TABLE login_table
    (id INT,
    name CHAR(10),
    time_in datetime,
    time_out datetime
    );
    INSERT INTO login_table VALUES
    (1,’happy’,’9:45:00′,’18:00:00′);
    (2,’navneet’,’9:30:00′,’19:00:00′);
    (3,’vicky’,10:15:00′,20:00:00′);

    output like this

    1 happy 1900-01-01 09:45:00.000 1900-01-01 18:00:00.000
    2 navneet 1900-01-01 09:30:00.000 1900-01-01 19:00:00.000
    3 vicky 1900-01-01 10:15:00.000 1900-01-01 20:00:00.000

    but i want to show only for time not for 1900-01-01

    2 quey i used

    CREATE view resultset AS
    SELECT
    *
    datediff(minute,cast(‘janv 1 1900 9:30AM’ AS datetime),
    cast(time_in AS datetime))AS l_time
    FROM login_table;

    then output

    Msg 102, Level 15, State 1, Procedure resultset, Line 4
    Incorrect syntax near ‘datediff’.

    plz help me this query

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s