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://blog.sqlauthority.com)
10 Comments. Leave new
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;
—
he can’t as he doesn’t know that the error was because of @param datatype problem
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
–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;
Nice article
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
/*
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;
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
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
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