CODEWITHSUNDEEP
sql-server-2005
user42348
user42348

Reputation: 4319

Concerned with Top in sql

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'.

Upvotes: 0

Views: 83

Answers (3)

van
van

Reputation: 76982

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

Upvotes: 2

codingbadger
codingbadger

Reputation: 43974

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

Upvotes: 1

Hans Kesting
Hans Kesting

Reputation: 39274

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.

Upvotes: 3

Related Questions