使用SqlDataAdapter执行查询时@parameterName附近出现语法错误求助

错误原因 #

SQL Server 中 TOP 关键字后不能直接跟参数变量，必须将参数放在括号内，也就是写成 TOP (@take) 才符合语法规范。

修正后的代码 #

con.Open();
string sql = "select top (@take) * from"+
"(Select ProductName, CategoryName, CompanyName, UnitPrice, ROW_NUMBER() OVER(ORDER BY ProductId) AS ROW_NUM "+
"from Products as p inner join Categories as c on p.CategoryID = c.CategoryID "+
"inner join Suppliers as s on p.SupplierID = s.SupplierID "+
") as x "+
"where x.ROW_NUM > @skip ";
SqlDataAdapter adapter = new SqlDataAdapter(sql, con);

adapter.SelectCommand.Parameters.Add("@take", SqlDbType.Int).Value = 20;
adapter.SelectCommand.Parameters.Add("@skip", SqlDbType.Int).Value = 10;

DataTable dt = new DataTable();
adapter.Fill(dt);
repProducts.DataSource = dt;
repProducts.DataBind();
con.Close();

更推荐的分页写法（OFFSET/FETCH） #

从SQL Server 2012开始，支持OFFSET ... FETCH NEXT的标准分页语法，比用ROW_NUMBER的写法更简洁：

con.Open();
string sql = "Select ProductName, CategoryName, CompanyName, UnitPrice "+
"from Products as p inner join Categories as c on p.CategoryID = c.CategoryID "+
"inner join Suppliers as s on p.SupplierID = s.SupplierID "+
"ORDER BY ProductId "+
"OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY";

SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
adapter.SelectCommand.Parameters.Add("@take", SqlDbType.Int).Value = 20;
adapter.SelectCommand.Parameters.Add("@skip", SqlDbType.Int).Value = 10;

DataTable dt = new DataTable();
adapter.Fill(dt);
repProducts.DataSource = dt;
repProducts.DataBind();
con.Close();

内容的提问来源于stack exchange，提问作者user1238784