以下是分页存储过程代码:--创建分页存储过程
create procedure proc_Sql_Paging
@SqlDataTable nvarchar(4000),--表名
@PrimaryKey nvarchar(4000),--主键名称
@Fields nvarchar(4000),--要返回的字段
@pageSize int,--页尺寸
@pageIndex int,--页码
@recordCount int output,--记录总数
@strOrderBy nvarchar(4000),--排序
@strWhere nvarchar(4000)--查询条件
AS
begin
set nocount on
declare @strSQL1 nvarchar(4000)--SQL语句1
declare @strSQL2 nvarchar(4000)--SQL语句2
declare @strSQL3 nvarchar(4000)--SQL语句3
set @strSQL1 = 'SELECT ' + @PrimaryKey + ', ROW_NUMBER() OVER (' + SUBSTRING(@strOrderBy,CHARINDEX('order',@strOrderBy),len(@strOrderBy)) + ') AS RowNumber FROM ' + @SqlDataTable + ' ' + @strWhere
--获取总记录数
set @strSQL3 = 'SELECT @recordCount = COUNT(*) FROM ' + @SqlDataTable + ' ' + @strWhere
exec SP_EXECUTESQL
@stmt = @strSQL3,
@params = N'@recordCount AS int output',
@recordCount = @recordCount output
--分页查询
if @pageIndex > @recordCount * 1.0 / @pageSize + 1.0 OR @recordCount <= @pageSize
begin
set @pageIndex = 1
end
set @strSQL2 = 'SELECT ' + @Fields + ' FROM ' + @SqlDataTable + ' WHERE ' + @PrimaryKey + ' IN (SELECT '+@PrimaryKey+' FROM ('+@strSQL1+') TempTable WHERE RowNumber BETWEEN ' + Str((@pageIndex - 1) * @pageSize + 1) + ' AND ' + Str(@pageIndex * @pageSize) + ') ' + @strOrderBy
exec SP_EXECUTESQL @strSQL2
end
GO我现在要用SP_EXECUTESQL来执行这个存储过程,但是写法一直是错误的。以下是我用的写法错误写法1:
declare @Count int
exec sp_executesql @stmt = stmt,
@params = N'proc_sql_Paging @SqlDataTable nvarchar(4000),@PrimaryKey nvarchar(4000),@Fields nvarchar(4000),@pageSize int,@pageIndex int,@recordCount int output,@strOrderBy nvarchar(4000),@strWhere nvarchar(4000)',
@param1 = 'Sys_Users',
@param2 = 'Use_Id',
@param3 = '*',
@param4 = '1',
@param5 = '1',
@param6 = @Count,
@param7 = 'Use_Id',
@param8 = ''
go
错误提示:消息 102,级别 15,状态 1,第 1 行
'proc_sql_Paging' 附近有语法错误。
错误写法2:
declare @Count int
exec sp_executesql @stmt = stmt,
@params = N'proc_sql_Paging,@SqlDataTable nvarchar(4000),@PrimaryKey nvarchar(4000),@Fields nvarchar(4000),@pageSize int,@pageIndex int,@recordCount int output,@strOrderBy nvarchar(4000),@strWhere nvarchar(4000)',
@param1 = 'Sys_Users,Use_Id,*,1,1,0,Use_Id,'
go
错误提示:消息 102,级别 15,状态 1,第 1 行
'proc_sql_Paging' 附近有语法错误。请教正确的写法SP_EXECUTESQL执行存储过程
create procedure proc_Sql_Paging
@SqlDataTable nvarchar(4000),--表名
@PrimaryKey nvarchar(4000),--主键名称
@Fields nvarchar(4000),--要返回的字段
@pageSize int,--页尺寸
@pageIndex int,--页码
@recordCount int output,--记录总数
@strOrderBy nvarchar(4000),--排序
@strWhere nvarchar(4000)--查询条件
AS
begin
set nocount on
declare @strSQL1 nvarchar(4000)--SQL语句1
declare @strSQL2 nvarchar(4000)--SQL语句2
declare @strSQL3 nvarchar(4000)--SQL语句3
set @strSQL1 = 'SELECT ' + @PrimaryKey + ', ROW_NUMBER() OVER (' + SUBSTRING(@strOrderBy,CHARINDEX('order',@strOrderBy),len(@strOrderBy)) + ') AS RowNumber FROM ' + @SqlDataTable + ' ' + @strWhere
--获取总记录数
set @strSQL3 = 'SELECT @recordCount = COUNT(*) FROM ' + @SqlDataTable + ' ' + @strWhere
exec SP_EXECUTESQL
@stmt = @strSQL3,
@params = N'@recordCount AS int output',
@recordCount = @recordCount output
--分页查询
if @pageIndex > @recordCount * 1.0 / @pageSize + 1.0 OR @recordCount <= @pageSize
begin
set @pageIndex = 1
end
set @strSQL2 = 'SELECT ' + @Fields + ' FROM ' + @SqlDataTable + ' WHERE ' + @PrimaryKey + ' IN (SELECT '+@PrimaryKey+' FROM ('+@strSQL1+') TempTable WHERE RowNumber BETWEEN ' + Str((@pageIndex - 1) * @pageSize + 1) + ' AND ' + Str(@pageIndex * @pageSize) + ') ' + @strOrderBy
exec SP_EXECUTESQL @strSQL2
end
GO我现在要用SP_EXECUTESQL来执行这个存储过程,但是写法一直是错误的。以下是我用的写法错误写法1:
declare @Count int
exec sp_executesql @stmt = stmt,
@params = N'proc_sql_Paging @SqlDataTable nvarchar(4000),@PrimaryKey nvarchar(4000),@Fields nvarchar(4000),@pageSize int,@pageIndex int,@recordCount int output,@strOrderBy nvarchar(4000),@strWhere nvarchar(4000)',
@param1 = 'Sys_Users',
@param2 = 'Use_Id',
@param3 = '*',
@param4 = '1',
@param5 = '1',
@param6 = @Count,
@param7 = 'Use_Id',
@param8 = ''
go
错误提示:消息 102,级别 15,状态 1,第 1 行
'proc_sql_Paging' 附近有语法错误。
错误写法2:
declare @Count int
exec sp_executesql @stmt = stmt,
@params = N'proc_sql_Paging,@SqlDataTable nvarchar(4000),@PrimaryKey nvarchar(4000),@Fields nvarchar(4000),@pageSize int,@pageIndex int,@recordCount int output,@strOrderBy nvarchar(4000),@strWhere nvarchar(4000)',
@param1 = 'Sys_Users,Use_Id,*,1,1,0,Use_Id,'
go
错误提示:消息 102,级别 15,状态 1,第 1 行
'proc_sql_Paging' 附近有语法错误。请教正确的写法SP_EXECUTESQL执行存储过程
DECLARE @param1 NVARCHAR(30),@param2 NVARCHAR(30),@param3 NVARCHAR(30)
DECLARE @param4 INT,@param5 INT,@param6 INT
DECLARE @param7 NVARCHAR(1000),@param8 NVARCHAR(1000)SET @params=N'EXEC proc_sql_Paging '
SET @params=@params +''''+@param1+''','''+@param2+''','''+@param3+''','+LTRIM(@param4)+','+LTRIM(@param5)+','+LTRIM(@param6)+','
SET @params=@params+''''+@param7+''','''+@param8+''''
EXEC(@params)