Concerned with Top in sql

Question

I have variable @count of datatype int.I am setting values to this @count.
I want to select top @count number of rows from table. When i use Select top @count, its showing error.

  IF (@NewItemCount<@OldItemCount)
    BEGIN
        set @count=@OldItemCount-@NewItemCount
        if(@count>0)
        BEGIN
            Delete from ItemDetails where  GroupId in (Select Top @count  Id from ItemDetails where GroupId=@Prm_GroupId )
        END
    END

The error is

Incorrect syntax near '@count'.

Answer 1

It is not possible to use a variable in this spot.

One solution would be to use dynamic sql: build the complete querystring that you want to execute in a string-variable and have that executed.

Answer 2

If you are going to go down the dynamic SQL route I would recommend you read this excellent article first

Edit:

Wrapping the @count variable in brackets should work for you:

 IF (@NewItemCount<@OldItemCount)
BEGIN
    set @count=@OldItemCount-@NewItemCount
    if(@count>0)
    BEGIN
        Delete from ItemDetails where  GroupId in (Select Top(@count)  Id from ItemDetails where GroupId=@Prm_GroupId )
    END
END

Answer 3

This works out of the box on SQL Server 2005 without any dynamic SQL.
You were just missing parenthesis. Following works like a charm:

DECLARE @CNT INT
SET @CNT = 5

SELECT  TOP (@CNT) *
FROM    MYTABLE