How to fix SqlException incorrect syntax near @p0 for Delete Top n command

Question

I have the following statement that is failing in a C# program using LINQ to Entity Framework 4:

int top = 1000;
string name = "StagingTable";

using (var context = CreateObjectContext())
{
    int count = context.ExecuteStoreCommand(string.Concat("DELETE TOP {0} FROM ", name), top);
}

The context is created correctly (used in other parts of the program) and the table name is spelled correctly. According to Microsoft documentation, this should work to delete a maximum number of records from the table, but instead throws an exception:

System.Data.SqlClient.SqlException: Incorrect syntax near @p0.

I checked and rechecked the syntax of the ExecuteStoreCommand and could not find anything wrong.

How is it possible to use the TOP clause in a DELETE statement like this?

Answer 1

When passing a parameter to TOP you need to enclose it in parentheses:

int count = context.ExecuteStoreCommand(string.Concat("DELETE TOP ({0}) FROM ", name), top);

Answer 2

I found an answer in a similar but unrelated post (MS SQL Exception: Incorrect syntax near '@P0') when a SELECT TOP statement was executed from Java.

"SQL Server requires you to place parenthesis around the argument to TOP" if you pass it as an argument.

So code that works is:

int top = 1000;
string name = "StagingTable";
using (var context = CreateObjectContext())
{
    int count = context.ExecuteStoreCommand(string.Concat("DELETE TOP ({0}) FROM ", name), top);
}

Thanks, Andomar.