set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Getnewsclass]
@pageSize int, --页大小
@pageIndex int, --页索引
@TableName varchar(50), --表明
@Filter varchar(1000) /*,
@rowsCount int output --总记录数*/
AS
BEGIN
--计算总行数
declare @rowsCount int
select @rowsCount = count(*) from @TableName declare @startRecord int, @maxRecord int
set @startRecord = (@pageIndex - 1) * @pageSize
set @maxRecord = @pageIndex * @pageSize
if (@startRecord >= @rowsCount and @startRecord >= @pageSize)
begin
set @startRecord = @startRecord - @pageSize
set @maxRecord = @maxRecord - @pageSize
end
declare @Statment nvarchar(4000)
set @Statment ='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY id) as rowNum,* from '+@TableName+' where '+@Filter+')
as t where
rowNum > '+cast(@startRecord as nvarchar)+' and
rowNum <= '+cast(@maxRecord as nvarchar)
exec sp_executesql @StatmentEND
消息 1087,级别 15,状态 2,过程 Getnewsclass,第 17 行
必须声明表变量 "@TableName"。
这东西不是很熟~!请指教~!
set QUOTED_IDENTIFIER ON
go-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Getnewsclass]
@pageSize int, --页大小
@pageIndex int, --页索引
@TableName varchar(50), --表明
@Filter varchar(1000) /*,
@rowsCount int output --总记录数*/
AS
BEGIN
--计算总行数
declare @rowsCount int
select @rowsCount = count(*) from @TableName declare @startRecord int, @maxRecord int
set @startRecord = (@pageIndex - 1) * @pageSize
set @maxRecord = @pageIndex * @pageSize
if (@startRecord >= @rowsCount and @startRecord >= @pageSize)
begin
set @startRecord = @startRecord - @pageSize
set @maxRecord = @maxRecord - @pageSize
end
declare @Statment nvarchar(4000)
set @Statment ='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY id) as rowNum,* from '+@TableName+' where '+@Filter+')
as t where
rowNum > '+cast(@startRecord as nvarchar)+' and
rowNum <= '+cast(@maxRecord as nvarchar)
exec sp_executesql @StatmentEND
消息 1087,级别 15,状态 2,过程 Getnewsclass,第 17 行
必须声明表变量 "@TableName"。
这东西不是很熟~!请指教~!
这个应该不用加引号和加号吧
应该如下:
ALTER PROCEDURE [dbo].[Getnewsclass]
(
@pageSize int, --页大小
@pageIndex int, --页索引
@TableName varchar(50), --表明
@Filter varchar(1000) /*,
@rowsCount int output --总记录数*/
)
AS
BEGIN
sp_executesql N'select @rowsCount = count(*) from '+@TableName,N'@rowsCount int output',@rowsCount output
set QUOTED_IDENTIFIER ON
go-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Getnewsclass]
@pageSize int, --页大小
@pageIndex int, --页索引
@TableName varchar(50), --表明
@Filter varchar(1000) /*,
@rowsCount int output --总记录数*/
AS
BEGIN
--计算总行数
declare @rowsCount int
--select @rowsCount = count(*) from @TableName
sp_executesql N'select @rowsCount = count(*) from '+@TableName,N'@rowsCount int output',@rowsCount output
declare @startRecord int, @maxRecord int
set @startRecord = (@pageIndex - 1) * @pageSize
set @maxRecord = @pageIndex * @pageSize
if (@startRecord >= @rowsCount and @startRecord >= @pageSize)
begin
set @startRecord = @startRecord - @pageSize
set @maxRecord = @maxRecord - @pageSize
end
declare @Statment nvarchar(4000)
set @Statment ='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY id) as rowNum,* from '+@TableName+' where '+@Filter+')
as t where
rowNum > '+cast(@startRecord as nvarchar)+' and
rowNum <= '+cast(@maxRecord as nvarchar)
exec sp_executesql @StatmentEND
'+' 附近有语法错误。
是传进来的,但在sql里执行的时候报这个错~!
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Getnewsclass]
@pageSize int, --页大小
@pageIndex int, --页索引
@TableName nvarchar(1000), --表明
@Filter nvarchar(1000) /*,
@rowsCount int output --总记录数*/
AS
BEGIN
--计算总行数
declare @rowsCount int
declare @strTmp nvarchar(1000)
set @strTmp=N'select @rowsCount=Count(id) FROM '+@TableName
exec sp_executesql @strTmp
declare @startRecord int, @maxRecord int
set @startRecord = (@pageIndex - 1) * @pageSize
set @maxRecord = @pageIndex * @pageSize
if (@startRecord >= @rowsCount and @startRecord >= @pageSize)
begin
set @startRecord = @startRecord - @pageSize
set @maxRecord = @maxRecord - @pageSize
end
declare @Statment nvarchar(4000)
set @Statment ='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY id) as rowNum,* from '+@TableName+' where '+@Filter+')
as t where
rowNum > '+cast(@startRecord as nvarchar)+' and
rowNum <= '+cast(@maxRecord as nvarchar)
exec sp_executesql @StatmentEND
必须声明标量变量 "@rowsCount"。
@rowsCount int output --总记录数*/
/*,被你注释掉了,哪还有@rowsCount
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Getnewsclass]
@pageSize int, --页大小
@pageIndex int, --页索引
@TableName nvarchar(1000), --表明
@Filter nvarchar(1000)
AS
BEGIN
--计算总行数
declare @rowsCount int
declare @strTmp nvarchar(1000)
set @strTmp='select @rowsCount=Count(id) FROM '+@TableName
exec sp_executesql @strTmp,N'@rowsCount int',@rowsCount
declare @startRecord int, @maxRecord int
set @startRecord = (@pageIndex - 1) * @pageSize
set @maxRecord = @pageIndex * @pageSize
if (@startRecord >= @rowsCount and @startRecord >= @pageSize)
begin
set @startRecord = @startRecord - @pageSize
set @maxRecord = @maxRecord - @pageSize
end
declare @Statment nvarchar(4000)
set @Statment ='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY id) as rowNum,* from '+@TableName+' where '+@Filter+')
as t where
rowNum > '+cast(@startRecord as nvarchar)+' and
rowNum <= '+cast(@maxRecord as nvarchar)
exec sp_executesql @StatmentEND