还是老问题，分页存储过程。（帮顶有分）
我用的是邹建的分页存储过程；
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE Proc [dbo].[p_show]
@QueryStr nvarchar(4000), --表名、视图名、查询语句
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (4000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
.........略
@QueryStr参数是一个查询语句sqlStr，而本身这个sqlStr也是带参数的。
我只能在程序里设置存储过程那5个参数的值，但没法设sqlStr这句sql语句中参数的值。请教各位高手解决方法。
[解决办法]
那你为什么不把那些参数也整合到PROC里?
[解决办法]
your are right!
but you can try:
@QueryStr="select * from tableName where col1='"+col1(C#)+"'";
[解决办法]
@QueryStr 这个可以是表名
在存储过程里写查询语句
[解决办法]
那就再建一个存储过程，在里面先设置sqlStr的参数，然后再调用分页存储过程
[解决办法]
把你需要的参数也传值到存储过程里，然后在里面拼接字符串就好了啊
[解决办法]
你可试试把.@QueryStr参数是一个查询语句sqlStr，而本身这个sqlStr也是带参数的 这两个都当作参数来试试..
[解决办法]
关注
[解决办法]
帮顶！
[解决办法]
人过留名
[解决办法]
- SQL code
ALTER PROCEDURE [dbo].[Components_MultiPage] @TableName NVARCHAR(MAX), @Fields NVARCHAR(MAX), @Where NVARCHAR(MAX), @OrderBy NVARCHAR(MAX), @Groupby NVARCHAR(MAX), @PageIndex INT, @PageSize INTASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @BeginIndex INT DECLARE @EndIndex INT DECLARE @Sql NVARCHAR(MAX) DECLARE @SqlCount NVARCHAR(MAX) DECLARE @GroupbyString NVARCHAR(MAX) IF(@Where = '') BEGIN SET @Where = '1 = 1' END SELECT @BeginIndex = (@PageIndex - 1) * @PageSize SELECT @EndIndex = @PageIndex * @PageSize IF(@Groupby <> N'') BEGIN SET @GroupbyString = N' GROUP BY ' + @Groupby END ELSE BEGIN SET @GroupbyString = N' ' END SET @Sql = N' BEGIN WITH TheTable AS( SELECT ' + @Fields + ', ROW_NUMBER() OVER(ORDER BY ' + @OrderBy + ') AS RowNumber FROM ' + @TableName + ' WHERE ' + @Where + ' ' + @GroupbyString + ') SELECT * FROM TheTable WHERE RowNumber > ' + CONVERT(NVARCHAR(255), @BeginIndex) + ' AND RowNumber <= ' + CONVERT(NVARCHAR(255), @EndIndex) + ' END' SET @SqlCount = N'SELECT COUNT(*) AS [Rows] FROM ' + @TableName + ' WHERE ' + @Where + ' ' -- PRINT @Sql-- PRINT @SqlCount EXECUTE SP_EXECUTESQL @Sql EXECUTE SP_EXECUTESQL @SqlCount-- DEBUG-- EXECUTE SP_EXECUTESQL dbo.Components_MultiPage 'User_Users', 'UserId', '', 'UserId', 1, 30END
[解决办法]
这是SQL的存储过程,不是原创的.
CREATE Procedure BENNY_QueryRecordByPage
(
@PageSize int, --一页多少记录
@PageNumber int, --第几页
@QuerySql varchar(1000),--???????,?* From Test order by id desc
@KeyField varchar(500)
)
AS
Begin
Declare @SqlTable AS varchar(1000)
Declare @SqlText AS Varchar(1000)
Declare @RCount AS int
Set @SqlTable='Select Top '+CAST(@PageNumber*@PageSize AS varchar(30))+' '+@QuerySql
Set @SqlText='Select Top '+Cast(@PageSize AS varchar(30))+' * From '
+'('+@SqlTable+') As TembTbA '
+'Where '+@KeyField+' Not In (Select Top '+CAST((@PageNumber-1)*@PageSize AS varchar(30))+' '+@KeyField+' From '
+'('+@SqlTable+') AS TempTbB)'
Exec(@SqlText)
End
[解决办法]
mark
[解决办法]
学习
[解决办法]
JF
[解决办法]
- C# code
/*--------------------------------------------------suyiming分页存储过程2007-10-22 16:47:26-------------------------------------------------*/CREATE proc UP_GetPageCount @tblName varchar(255), -- 表名 @fldName varchar(255), -- 字段名 @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @IsCount bit = 0, -- 返回记录总数, 非 0 值则返回 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 @strWhere varchar(1000) ='' -- 查询条件 (注意: 不要加 where) AS declare @strSQL varchar(1000) -- 主语句 declare @strTmp varchar(300) -- 临时变量 declare @strOrder varchar(400) if @OrderType != 0 begin set @strTmp ='<(select min' set @strOrder ='order by [' + @fldName +'] desc'end else begin set @strTmp ='>(select max' set @strOrder =' order by [' + @fldName +'] asc'end /*----------------------------------------------- 简化: -------------select top str(10) * from [tblName] where [fldName] >(select min([fldName]) from (select top str(1-1)*10) [fldName] from [tblname] order by [fldName] asc as tblTmp-------------------------------------------------*/set @strSQL ='select top ' + str(@PageSize) + ' * from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' + @strOrderif @strWhere !='' set @strSQL ='select top ' + str(@PageSize) + ' * from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + '] where (' + @strWhere + ') ' + @strOrder + ') as tblTmp) and (' + @strWhere + ') ' + @strOrderif @PageIndex = 1 begin set @strTmp =' ' if @strWhere !=' ' set @strTmp =' where (' + @strWhere + ')' set @strSQL ='select top ' + str(@PageSize) + ' * from [' + @tblName + ']' + @strTmp + ' ' + @strOrderend if @IsCount != 0 set @strSQL ='select count(*) as Total from [' + @tblName + ']'exec (@strSQL) GO
[解决办法]
顶拉顶拉.
[解决办法]
我也来顶一下