关于存储过程的小问题

[xjl1314521wxw]

什么是分页存储过程？详细定义是什么？分页体现在什么 地方？最好举例说明一下！
再问一个！
sqldatasource控件里，如果我调用的是写好的存储过程，但这个程序过程有参数，面这些参数来自Gridview控件，不知道怎么实现传递参数！我知道可以用CONTROL然后设置参数控件，但是，只有SELECT可以通过下一步设置，UPDATE就不行了，求解决方案！

[xiaosuper]

up

[seesea125]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



/*use sample:
declare @totalRecord int,
@return_value int
exec @return_value=[PAKS_sp_CommonPage] 'PAKS_News g inner join PAKS_Item i on g.id=i.contentid',
'g.id,g.title','g.LastUpdateDate desc','',8,1,1,@totalRecord=@totalRecord output
print @return_value
*/
create PROCEDURE [dbo].[PAKS_sp_CommonPage]
@TableName nvarchar(500), --Table Name
@Fields nvarchar(4000) = '*', --Fields Name(ALL Fields Is *)
@OrderField nvarchar(4000), --Order Fields(May Multi Field)
@sqlWhere nvarchar(4000) = Null, --Conditional statements(Not Need where)
@pageSize int, --The number of records per page
@pageIndex int = 1 , --Specify the current pages
@totalflag bit=0,
--The total number of pages returned
@totalRecord int output --Total number of records
as
begin
Begin tran --Begin transaction
declare @sql nvarchar(4000);
declare @TotalPage int;
set @totalRecord=-2

--Calculating the total number of records
if (@SqlWhere='' or @sqlWhere=null)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from '
+ @TableName + ' where ' + @sqlWhere
exec sp_executesql @sql,N'@totalRecord int output',@totalRecord output
--print @totalRecord
if(@totalflag=0)
begin
--Calculating the total number of pages
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'select * from (select row_number() over(order by ' + @OrderField
+ ') as rowId,' + @Fields + ' from ' + @TableName
else
set @sql = 'select * FROM (select row_number() over(order by ' + @OrderField
+ ') as rowId,' + @Fields + ' from ' + @TableName + ' where '
+ @SqlWhere
--Page out of range of treatment
if @PageIndex<=0
set @pageIndex = 1
--Starting point and end point of treatment
declare @StartRecord int
declare @EndRecord int
set @StartRecord = (@pageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @pageSize - 1
--Continued synthesis of sql statement
set @Sql = @Sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord)
+ ' and ' + Convert(varchar(50),@EndRecord)
--print @sql
exec(@Sql)
---------------------------------------------------

end



If @@Error <> 0
begin
rollBack tran
return -1
end
else
begin
commit tran

return @totalRecord ---The total number of returned records
end
end

[jack15850798154]

create procedure XiaoZhengGe
@sqlstr nvarchar(4000), --查询字符串
@currentpage int,--第N页
@pagesize int--每页行数
as
set nocount on
declare @P1 int,--P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off

[jack15850798154]

（2）另一种：
CREATE procedure Department_pagination
@SelectStr nvarchar(1000),
@ColumnStr nvarchar (1000),
@OrderStr nvarchar (1000),
@CurrentPage int,
@PageCount int
as
declare @TimeName nvarchar(25)
declare @TableStr nvarchar(1000)

select @TimeName = convert(nvarchar(23), getdate(), 121)
set @TimeName = REPLACE(@TimeName, '.', '')
set @TimeName = REPLACE(@TimeName, ':', '')
set @TimeName = REPLACE(@TimeName, '-', '')
set @TimeName = REPLACE(@TimeName, ' ', '')

select @TableStr='create table ##Tab' + @TimeName + '(wb int identity,'
exec(@TableStr+@ColumnStr+')')
exec('insert into ##Tab' + @TimeName + ' ' + @SelectStr + ' order by ' + @OrderStr)
exec('select * from ##Tab' + @TimeName + ' where wb between ((' + @CurrentPage + '-1)*' + @PageCount + '+1) and ' + @CurrentPage + '*' + @PageCount + ' order by wb')
exec('drop table ##Tab' + @TimeName)
GO



参数1：select语句。2：字段列表。3：排序字段。4：当前页。5每页数目

[凤凰涅檠]

上面的是我回答的，这个号专门用于接分，谢谢

[zhulong1111]

  if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_Pagediv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_Pagediv]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE  PROCEDURE SP_Pagediv
@tblName   varchar(3000),       -- 表名
@strGetFields varchar(3000) = '*',  -- 需要返回的列
@fldName varchar(255)='',      -- 排序的字段名
@PageSize   int = 10,          -- 页尺寸
@PageIndex  int = 1,           -- 页码
@doCount  bit = 0,   -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0,  -- 设置排序类型, 非 0 值则降序
@strWhere  varchar(1500) = ''  -- 查询条件 (注意: 不要加 where)

AS

declare @strSQL   varchar(5000)       -- 主语句

declare @strTmp   varchar(110)        -- 临时变量

declare @strOrder varchar(400)        -- 排序类型


--  begin
--   replace(@strGetFields,'''','''''')
--  end
--  

if @doCount != 0
  begin
    if @strWhere !=''
     set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
    else
  set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
  end
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况

else

begin

 

if @OrderType != 0

begin

    set @strTmp = '<(select min'

set @strOrder = ' order by [' + @fldName +'] desc'

--如果@OrderType不是0，就执行降序，这句很重要！

end

else

begin

    set @strTmp = '>(select max'

    set @strOrder = ' order by [' + @fldName +'] asc'

end

 

if @PageIndex = 1

begin

    if @strWhere != ''  

    set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder

     else

     set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ['+ @tblName + '] '+ @strOrder

--如果是第一页就执行以上代码，这样会加快执行速度

end

else

begin

--以下代码赋予了@strSQL以真正执行的SQL代码

set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '

    + @tblName + ' where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from ' + @tblName + ' ' + @strOrder + ') as tblTmp)'+ @strOrder

 

if @strWhere != ''

    set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '

        + @tblName + ' where [' + @fldName + ']' + @strTmp + '(['

        + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['

        + @fldName + '] from ' + @tblName + ' where ' + @strWhere + ' '

        + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder

end

end  

exec (@strSQL)



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

[zhulong1111]

[Quote=引用 7 楼 ch_weni 的回复:]
上面的是我回答的，这个号专门用于接分，谢谢
[/Quote]哥们 当人家都是瓜娃子啊

[coco_ke]

调用存储过程的时候参数源可以来自Control,cookies,form,profile,Querystring,session 当你需要的参数不能够被Sqldatasoure自动识别的时候，可以把该参数Add到Session，然后取Session

[newdigitime]

分页存储,主要思路就是每次到数据库只查询供一页显示的数据.而普通的搞法是把所有符合条件的数据都读出来,但只是显示一页.这就弄成了不必要的浪费.当然你可以通过缓存降低负页影响,不过不当的缓存策略会造成
内存被滥用.

分页存储很简单,其精华就在于如何提高速度/效率.

update 参数一般由Gridview的各列的当前值 自动向sqldatasource提供.