Search code examples
sql-serverstored-proceduresdeclareclause

Syntax error when assign a variable for top clause in sql server

I need to get top 10, top 100 records like this from a table. But I need to assign the value for top clause such as 10 or 100 in a variable.

But when I give like below, it gives an syntax error "Incorrect syntax near '@numberofRecords'.".

DECLARE @numberofRecords INT
SELECT @numberofRecords = ConfigValue FROM tblConfigItems (NOLOCK) WHERE configName = 'TopRecords'

SELECT TOP @numberofRecords [ID],TypeID, MessageType, operationDate
FROM NotifyTbl (NOLOCK)
wHERE STATUS IN ('1', '2')

How to achieve this?

Solution

  • Avoid dinamic SQL. Just use this:

    SELECT TOP (@numberofRecords) [ID],TypeID, MessageType, operationDate
FROM NotifyTbl (NOLOCK)
wHERE STATUS IN ('1', '2')

    You need at least SQL 2005 to have it working. Yes () does the trick.