找到一个好用的分页存储过程，请教如何获取它的返回输出参数，类似于。。。

[luoxp520]

找到一个好用的分页存储过程，请教如何获取它的返回输出参数，类似于

SqlCommand Command = new SqlCommand();
            Command.Connection = dbClass.Con;
            Command.CommandText = "docount";
            Command.CommandType = CommandType.StoredProcedure;
            Command.Parameters.AddWithValue("@tblName", talName);
            Command.Parameters.AddWithValue("@strWhere", (strWhere != "" && strWhere != null) ? "where " + strWhere : strWhere);//条件要加where
            Command.Parameters.AddWithValue("@fldName", fldName);
            SqlParameter workParm;
            workParm = Command.Parameters.AddWithValue("@RecordCount", SqlDbType.Int);
            workParm.Direction = ParameterDirection.Output;
            if (Command.Connection.State == ConnectionState.Closed)
                Command.Connection.Open();
            Command.ExecuteScalar();
            Command.Connection.Close();
            Int32 RecordCount = (Int32)Command.Parameters["@RecordCount"].Value;

CREATE procedure select_pagesize
(
@select_list varchar(8000),--不需要select
@table_name varchar(8000),
@where varchar(8000),--不需要where
@primary_key varchar(8000),--当是表联合时，加表名前缀.
@order_by varchar(8000),--需要完整的子句 order by ...
@page_size smallint,--每页记录
@page_index int,--页索引
@do_count bit)--1只统计总数
as
/*
过程名：通用存储过程分页
使用示例:
单表sql调用：exec select_pagesize 'login_id,login_name','tb_login',' login_name like ''%%''','login_id',' order by login_dt desc',20,10
多表sql调用：exec select_pagesize 'a.login_id,a.login_name,b.pro_name','tb_login a,tb_code_province b',' a.pro_id=b.pro_id and a.login_name like ''%%''','a.login_id',' order by a.login_dt desc',20,10
备注：外部程序调用不需要转义单引号
原型结构：select top 20 select_list
  from tablename
  where z_id not in(select z_id from (select top 100 z_id from tablename order by order_by) temptable)
      and ...
  order by order_by

*/

declare @sql_str varchar(8000)
declare @record_min int
declare @new_where varchar(8000),@newin_where varchar(8000)
if @where=''--重新为梳理，此过程时性能的考虑，因此不使用 where 1=1 再追加条件。
begin
select @new_where=''
select @newin_where=''
end
else
begin
select @new_where=' and '+@where
select @newin_where=' where '+@where
end

if @do_count=1
select @sql_str='select count(*) from '+@table_name+@newin_where
else
if @page_index=1
if @where=''
select @sql_str='select top '+convert(varchar,@page_size)+ ' '+@select_list+' from '+@table_name+' '+@order_by
else
select @sql_str='select top '+convert(varchar,@page_size)+ ' '+@select_list+' from '+@table_name+' where '+@where+' '+@order_by
else
begin
select @record_min=(@page_index-1)*@page_size
select @sql_str='select top '+convert(varchar,@page_size)+' '+@select_list+' from '+@table_name+' where '+@primary_key+' not in (select '+stuff(@primary_key,1,charindex('.',@primary_key),'')
select @sql_str=@sql_str+' from (select top '+convert(varchar,@record_min)+' '+@primary_key+' from '+@table_name+@newin_where+' '+@order_by+') temptable0000)'
select @sql_str=@sql_str+@new_where+' '+@order_by
end
--print @sql_str
exec(@sql_str)
GO

[wanghao3616]

cmd.Parameters["参数名"].Value.ToString();

[cpp2017]

它没有输出参数，只返回一个结果集。

另外这个分页存储过程有处硬伤，当多表联合查询,没有主键的时候，分页就会出问题。

[luoxp520]

请教有更好的分页存储过程吗

[luoxp520]

select_pagesize返回的是一个结果集
怎么得到它呢

[luoxp520]

执行select_pagesize存储过程时
因为查询条件字符很长
提示“允许从数据类型 ntext 到 varchar 的隐性转换。请使用 CONVERT 函数来运行此查询。 ”

 1 = 1  and Sort = 15 and right(''000''+cast(Sort as varchar(3)),3)+right(''000''+cast(Small_Sort as varchar(3)),3) in (015005,015010,015015,015020,015025,015030,015035,015040,015045,015050) and right(''000''+cast(Sort as varchar(3)),3)+right(''000''+cast(Small_Sort as varchar(3)),3)+right(''000''+cast(Three_Sort as varchar(3)),3) in (015005005,015005010,015005015,015005020,015005025,015005030,015010005,015010010,015010015,015010020,015010025,015010030,015015005,015015010,015015015,015015020,015015025,015015030,015020005,015020010,015020015,015020020,015020025,015020030,015025005,015025010,015025015,015025025,015025025,015025030,015030005,015030010,015030015,015030020,015030025,015030030,015035005,015035010,015035015,015035020,015035025,015035030,015040005,015040010,015040015,015040020,015040025,015040030,015045005,015045010,015045015,015045020,015045025,015045030,015050005,015050010,015050015,015050020,015050025,015050030) and right(''000''+cast(Sort as varchar(3)),3)+right(''000''+cast(small_sort as varchar(3)),3)+right(''000''+cast(three_sort as varchar(3)),3) in (005000000,010000000,015000000,020000000,025000000,030000000,035000000,040000000,045000000,055000000,060000000,065000000,070000000,075000000,085000000,090000000,095000000,100000000,005000000,005005000,005010000,005015000,005020000,005025000,010000000,010005000,010010000,015000000,015005000,015010000,015015000,015020000,015025000,015030000,015035000,015040000,015045000,015050000,020000000,020005000,020010000,020015000,020020000,020025000,020030000,025000000,025005000,025010000,030000000,030005000,030010000,030015000,030020000,035000000,035005000,035010000,035015000,040000000,040005000,040010000,040015000,040020000,045000000,055000000,055005000,055010000,055015000,055020000,055025000,060000000,060005000,060010000,065000000,065005000,065010000,065015000,065020000,065025000,065030000,065035000,065040000,065045000,065050000,070000000,070005000,070010000,070015000,070020000,070025000,070030000,075000000,075005000,075010000,085000000,090000000,095000000,100000000,005000000,005005000,005010000,005015000,005020000,005025000,010000000,010005000,010010000,015000000,015005000,015005005,015005010,015005015,015005020,015005025,015005030,015010000,015010005,015010010,015010015,015010020,015010025,015010030,015015000,015015005,015015010,015015015,015015020,015015025,015015030,015020000,015020005,015020010,015020015,015020020,015020025,015020030,015025000,015025005,015025010,015025015,015025025,015025025,015025030,015030000,015030005,015030010,015030015,015030020,015030025,015030030,015035000,015035005,015035010,015035015,015035020,015035025,015035030,015040000,015040005,015040010,015040015,015040020,015040025,015040030,015045000,015045005,015045010,015045015,015045020,015045025,015045030,015050000,015050005,015050010,015050015,015050020,015050025,015050030,020000000,020005000,020010000,020015000,020020000,020025000,020030000,025000000,025005000,025010000,030000000,030005000,030010000,030015000,030020000,035000000,035005000,035010000,035015000,040000000,040005000,040010000,040015000,040020000,045000000,055000000,055005000,055010000,055015000,055020000,055025000,060000000,060005000,060010000,065000000,065005000,065005005,065005010,065005015,065005020,065005025,065005030,065010000,065010005,065010010,065010015,065010020,065010025,065010030,065015000,065015005,065015010,065015015,065015020,065015025,065015030,065020000,065020005,065020010,065020015,065020020,065020025,065020030,065025000,065025005,065025010,065025015,065025020,065025025,065025030,065030000,065030005,065030010,065030015,065030020,065030025,065030030,065035000,065035005,065035010,065035015,065035020,065035025,065035030,065040000,065040005,065040010,065040015,065040020,065040025,065040030,065045000,065045005,065045010,065045015,065045020,065045025,065045030,065050000,065050005,065050010,065050015,065050020,065050025,065050030,070000000,070005000,070010000,070015000,070020000,070025000,070030000,075000000,075005000,075010000,085000000,090000000,095000000,100000000) 

[luoxp520]

现在对存储过程进行了处理
但是查询分析器中执行得到正常的结果
可程序里却还是老错误
快晕了。。
请看
http://topic.csdn.net/u/20080910/16/1afd2e93-ed85-4682-a511-3bfa4ff1ff27.html

[编程有钱人了]

UP