DataTable dtRtn = null;
pageCount = 0;
string strSql = "Select top(@pageIndex+@pageSize) @selectColumn1, IDENTITY(int, 1, 1) as row_number Into #t1 From @table WITH(NOLOCK) Where @where Group By @groupby Order By @orderby;";
strSql += "Select top(@pageSize) @selectColumn2 From #t1 Where row_number>@pageIndex;";
strSql += "select count(1) From @table Where @where;";
strSql += "truncate table #t1;";
strSql += "drop table #t1;";
DBCommandWrapper cmd = DB.BbsConfig_R.GetSqlStringCommandWrapper(strSql);
cmd.AddInParameter("@pageIndex", DbType.Int32, pageIndex * 10);
cmd.AddInParameter("@pageSize", DbType.Int32, pageSize);
cmd.AddParameter("@selectColumn1", DbType.String, 200, ParameterDirection.Input, true, 0, 0, null, DataRowVersion.Default, selectColumn1);
cmd.AddParameter("@table", DbType.String, 30, ParameterDirection.Input, true, 0, 0, null, DataRowVersion.Default, table);
cmd.AddParameter("@where", DbType.String, 200, ParameterDirection.Input, true, 0, 0, null, DataRowVersion.Default, where);
cmd.AddParameter("@groupby", DbType.String, 200, ParameterDirection.Input, true, 0, 0, null, DataRowVersion.Default, groupby);
cmd.AddParameter("@orderby", DbType.String, 200, ParameterDirection.Input, true, 0, 0, null, DataRowVersion.Default, orderby);
cmd.AddParameter("@selectColumn2", DbType.String, 200, ParameterDirection.Input, true, 0, 0, null, DataRowVersion.Default, selectColumn2);
DataSet ds = DB.BbsConfig_R.ExecuteDataSet(cmd);提示异常:System.Data.SqlClient.SqlException: 关键字 'with' 附近有语法错误。如果此语句是公用表表达式、xmlnamespaces 子句或者更改跟踪上下文子句,那么前一个语句必须以分号结尾。
在应使用条件的上下文(在 ';' 附近)中指定了非布尔类型的表达式。请问是为什么?谢谢!
pageCount = 0;
string strSql = "Select top(@pageIndex+@pageSize) @selectColumn1, IDENTITY(int, 1, 1) as row_number Into #t1 From @table WITH(NOLOCK) Where @where Group By @groupby Order By @orderby;";
strSql += "Select top(@pageSize) @selectColumn2 From #t1 Where row_number>@pageIndex;";
strSql += "select count(1) From @table Where @where;";
strSql += "truncate table #t1;";
strSql += "drop table #t1;";
DBCommandWrapper cmd = DB.BbsConfig_R.GetSqlStringCommandWrapper(strSql);
cmd.AddInParameter("@pageIndex", DbType.Int32, pageIndex * 10);
cmd.AddInParameter("@pageSize", DbType.Int32, pageSize);
cmd.AddParameter("@selectColumn1", DbType.String, 200, ParameterDirection.Input, true, 0, 0, null, DataRowVersion.Default, selectColumn1);
cmd.AddParameter("@table", DbType.String, 30, ParameterDirection.Input, true, 0, 0, null, DataRowVersion.Default, table);
cmd.AddParameter("@where", DbType.String, 200, ParameterDirection.Input, true, 0, 0, null, DataRowVersion.Default, where);
cmd.AddParameter("@groupby", DbType.String, 200, ParameterDirection.Input, true, 0, 0, null, DataRowVersion.Default, groupby);
cmd.AddParameter("@orderby", DbType.String, 200, ParameterDirection.Input, true, 0, 0, null, DataRowVersion.Default, orderby);
cmd.AddParameter("@selectColumn2", DbType.String, 200, ParameterDirection.Input, true, 0, 0, null, DataRowVersion.Default, selectColumn2);
DataSet ds = DB.BbsConfig_R.ExecuteDataSet(cmd);提示异常:System.Data.SqlClient.SqlException: 关键字 'with' 附近有语法错误。如果此语句是公用表表达式、xmlnamespaces 子句或者更改跟踪上下文子句,那么前一个语句必须以分号结尾。
在应使用条件的上下文(在 ';' 附近)中指定了非布尔类型的表达式。请问是为什么?谢谢!
Select top(@pageIndex+@pageSize) @selectColumn1, IDENTITY(int, 1, 1) as row_number Into #t1 From @table WITH(NOLOCK) Where @where Group By @groupby Order By @orderby;这句放查询分析器里 执行。。
SELECT * FROM table WITH (HOLDLOCK)