alter PROCEDURE [TB_TB_PRODUCTS_INFO_GETPAGED]
(
@TwoClass varchar(5),--2级分类
@ThreeClass varchar(5),--3级分类
@BRANDID varchar(5),--品牌
@Typevalues nvarchar(100),--类型属性值
@Price1 varchar(10),--价格1
@Price2 varchar(10),--价格2
@PAGEINDEX int,
@PAGESIZE int,
@ORDERBY nvarchar(100),
@RecordCount int OUTPUT
)
AS IF @PAGEINDEX IS NULL OR @PAGEINDEX <= 0
SET @PAGEINDEX = 1
IF @PAGESIZE IS NULL OR @PAGESIZE <= 0
SET @PAGESIZE = 24
IF @ORDERBY IS NULL OR len(@ORDERBY) = 0
SET @ORDERBY = '[CREATETIME] DESC'
SELECT @RecordCount = COUNT([ID]) FROM [TB_PRODUCTS_INFO] WHERE
STATE=1 AND
(@BRANDID IS NULL OR [BRANDID] = @BRANDID ) AND
(@Price1 IS NULL OR [ID] in(select PRODUCTID from TB_PRODUCTS_SUBSIDIARY where SALEPRICE >=@Price1)) AND
(@Price2 IS NULL OR [ID] in(select PRODUCTID from TB_PRODUCTS_SUBSIDIARY where SALEPRICE <=@Price2)) AND
(@TwoClass IS NULL OR [CLASSID] IN (SELECT [ID] FROM [TB_PRODUCTS_CLASS] WHERE [HIGHERID] = @TwoClass)) AND
(@ThreeClass IS NULL OR [CLASSID] = @ThreeClass)and
(@Typevalues is null or [ID] in (select PRODUCTID from TB_PRODUCTS_ATTRIVUTEVALUE where charindex(@Typevalues,VALUELISTS)>0))
--设置PAGEINDEX(如果超出最大页的时候)
IF @RecordCount <> 0 AND (@PAGEINDEX - 1)*@PAGESIZE > @RecordCount
SET @PAGEINDEX = ceiling(@RecordCount*1.0/@PAGESIZE)
DECLARE @Sql nvarchar(4000)
SET @Sql = N'
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY [CREATETIME] DESC ) AS RowId FROM [TB_PRODUCTS_INFO] WHERE STATE=1 AND
(@BRANDID IS NULL OR [BRANDID] = @BRANDID ) AND
(@Price1 IS NULL OR [ID] in(select PRODUCTID from TB_PRODUCTS_SUBSIDIARY where SALEPRICE >=@Price1)) AND
(@Price2 IS NULL OR [ID] in(select PRODUCTID from TB_PRODUCTS_SUBSIDIARY where SALEPRICE <=@Price2)) AND
(@TwoClass IS NULL OR [CLASSID] IN (SELECT [ID] FROM [TB_PRODUCTS_CLASS] WHERE [HIGHERID] = @TwoClass)) AND
(@ThreeClass IS NULL OR [CLASSID] = @ThreeClass) and
(@Typevalues is null or [ID] in (select PRODUCTID from TB_PRODUCTS_ATTRIVUTEVALUE where charindex(@Typevalues,VALUELISTS)>0))
) it left join [TB_PRODUCTS_SUBSIDIARY] su on it.ID=su.PRODUCTID
WHERE it.[RowId] BETWEEN (@PAGEINDEX - 1) * @PAGESIZE + 1 AND @PAGEINDEX * @PAGESIZE order by ' + @ORDERBY
EXEC sp_executesql @Sql,
N'@TwoClass varchar(5),@ThreeClass varchar(5),@BRANDID varchar(5), @Typevalues nvarchar(100), @Price1 varchar(10),@Price2 varchar(10),@PAGEINDEX int,@PAGESIZE int',
@TwoClass, @ThreeClass ,@BRANDID, @Typevalues, @Price1 ,@Price2 ,@PAGEINDEX ,@PAGESIZE 为上面这个存储过程在查询分析器可以 用程序来就不行了.....
(
@TwoClass varchar(5),--2级分类
@ThreeClass varchar(5),--3级分类
@BRANDID varchar(5),--品牌
@Typevalues nvarchar(100),--类型属性值
@Price1 varchar(10),--价格1
@Price2 varchar(10),--价格2
@PAGEINDEX int,
@PAGESIZE int,
@ORDERBY nvarchar(100),
@RecordCount int OUTPUT
)
AS IF @PAGEINDEX IS NULL OR @PAGEINDEX <= 0
SET @PAGEINDEX = 1
IF @PAGESIZE IS NULL OR @PAGESIZE <= 0
SET @PAGESIZE = 24
IF @ORDERBY IS NULL OR len(@ORDERBY) = 0
SET @ORDERBY = '[CREATETIME] DESC'
SELECT @RecordCount = COUNT([ID]) FROM [TB_PRODUCTS_INFO] WHERE
STATE=1 AND
(@BRANDID IS NULL OR [BRANDID] = @BRANDID ) AND
(@Price1 IS NULL OR [ID] in(select PRODUCTID from TB_PRODUCTS_SUBSIDIARY where SALEPRICE >=@Price1)) AND
(@Price2 IS NULL OR [ID] in(select PRODUCTID from TB_PRODUCTS_SUBSIDIARY where SALEPRICE <=@Price2)) AND
(@TwoClass IS NULL OR [CLASSID] IN (SELECT [ID] FROM [TB_PRODUCTS_CLASS] WHERE [HIGHERID] = @TwoClass)) AND
(@ThreeClass IS NULL OR [CLASSID] = @ThreeClass)and
(@Typevalues is null or [ID] in (select PRODUCTID from TB_PRODUCTS_ATTRIVUTEVALUE where charindex(@Typevalues,VALUELISTS)>0))
--设置PAGEINDEX(如果超出最大页的时候)
IF @RecordCount <> 0 AND (@PAGEINDEX - 1)*@PAGESIZE > @RecordCount
SET @PAGEINDEX = ceiling(@RecordCount*1.0/@PAGESIZE)
DECLARE @Sql nvarchar(4000)
SET @Sql = N'
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY [CREATETIME] DESC ) AS RowId FROM [TB_PRODUCTS_INFO] WHERE STATE=1 AND
(@BRANDID IS NULL OR [BRANDID] = @BRANDID ) AND
(@Price1 IS NULL OR [ID] in(select PRODUCTID from TB_PRODUCTS_SUBSIDIARY where SALEPRICE >=@Price1)) AND
(@Price2 IS NULL OR [ID] in(select PRODUCTID from TB_PRODUCTS_SUBSIDIARY where SALEPRICE <=@Price2)) AND
(@TwoClass IS NULL OR [CLASSID] IN (SELECT [ID] FROM [TB_PRODUCTS_CLASS] WHERE [HIGHERID] = @TwoClass)) AND
(@ThreeClass IS NULL OR [CLASSID] = @ThreeClass) and
(@Typevalues is null or [ID] in (select PRODUCTID from TB_PRODUCTS_ATTRIVUTEVALUE where charindex(@Typevalues,VALUELISTS)>0))
) it left join [TB_PRODUCTS_SUBSIDIARY] su on it.ID=su.PRODUCTID
WHERE it.[RowId] BETWEEN (@PAGEINDEX - 1) * @PAGESIZE + 1 AND @PAGEINDEX * @PAGESIZE order by ' + @ORDERBY
EXEC sp_executesql @Sql,
N'@TwoClass varchar(5),@ThreeClass varchar(5),@BRANDID varchar(5), @Typevalues nvarchar(100), @Price1 varchar(10),@Price2 varchar(10),@PAGEINDEX int,@PAGESIZE int',
@TwoClass, @ThreeClass ,@BRANDID, @Typevalues, @Price1 ,@Price2 ,@PAGEINDEX ,@PAGESIZE 为上面这个存储过程在查询分析器可以 用程序来就不行了.....
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
} /// <summary>
/// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
if (parameter != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
} return command;
}
这是调用方法.. 为什么了>!
@ORDERBY nvarchar(100),
@RecordCount int OUTPUT
)
AS
begin -----!!!
IF @PAGEINDEX IS NULL OR @PAGEINDEX <= 0
SET @PAGEINDEX = 1
IF @PAGESIZE IS NULL OR @PAGESIZE <= 0
SET @PAGESIZE = 24
IF @ORDERBY IS NULL OR......end