Published on: 10 February, 2014

How to use a parameter in Select TOP statement in SQL Server?

Tumblr Digg Google Plus

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

Comments

There are no comments available.

Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.