Integer arguments set to SQL Server as float

Questions
1

Hi,
The final line of the function below fails with “The number of rows provided for a TOP or FETCH clauses row count parameter must be an integer.” It’s sending

exec sp_executesql N’SELECT SUM(val) FROM (SELECT TOP ( @P0 ) val FROM temp123 ORDER BY val DESC) as tmp’,N’@P0 float’,2

to SQL. It works if you change the “float” to “int”. The same error occurs when changed to plugins.rawSQL.executeSQL().

Can anyone think of a workaround, or should I report it as an error?

John

function Top_n_Arg_Error() {
	let cServerName = 'YourServer';
	let cSql = "DROP TABLE IF EXISTS temp123; SELECT 1 AS val INTO temp123 UNION SELECT 2 UNION SELECT 3";
	plugins.rawSQL.executeSQL(cServerName, cSql, []);
	cSql = "SELECT * FROM temp123";
	let ds = databaseManager.getDataSetByQuery(cServerName, cSql, [], -1);
	application.output(ds);
	cSql = "SELECT SUM(val) FROM (SELECT TOP (2) val FROM temp123 ORDER BY val DESC) as tmp";
	ds = databaseManager.getDataSetByQuery(cServerName, cSql, [], -1);
	application.output(ds);
	cSql = "SELECT SUM(val) FROM (SELECT TOP (?) val FROM temp123 ORDER BY val DESC) as tmp";
	let aArgs = [2];
//	plugins.rawSQL.executeSQL(cServerName, cSql, aArgs);
	ds = databaseManager.getDataSetByQuery(cServerName, cSql, aArgs, -1);
}
2

Hi John,

You can try the approach below; it should resolve the issue.

Please cast the parameter to an integer when using it with the TOP clause, as SQL Server requires an integer value.

SELECT SUM(val)
FROM (
SELECT TOP (CAST(? AS INT)) val
FROM temp123
ORDER BY val DESC
) AS tmp;
let aArgs = [2];
ds = databaseManager.getDataSetByQuery(cServerName, cSql, aArgs, -1);

This ensures the parameter is treated as an integer rather than a float, which should prevent the error.

Thanks
Aijaz Ali

3

Perfect. Thank you.

John

1 Like