Many of you may wonder why the parameter that you try to pass does not work when you try to use Select Top Statement.
See below sample stored procedure that will accept a parameter for @NoOfRecords to retrieve the first top 10 records.
CREATE PROCEDURE GetProducts @NoOfRecords INT AS SELECT TOP @NoOfRecords * FROM Products GO
Alternative you can use an inline sql string, where you pass the parameter as integer and convert them into the string. Below is another alternative, but it is NOT RECOMMENDED
CREATE PROCEDURE GetProducts @NoOfRecords INT AS EXEC ('SELECT TOP ' + CONVERT(nvarchar,@NoOfRecords) + ' * FROM Products ') GO
Another clean solution is pretty simple, in order to fix the above issue, you can simply add a bracket surrounding the parameter. This method will avoid you to use inline SQL string that required CONVERT or CAST Method