declare @vssql nvarchar(1200)
select @vssql='insert into #t select ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice from from dbo.[Products]'+@Where
exec (@vssql)
select @vssql='insert into #t select ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice from from dbo.[Products]'+@Where
exec (@vssql)
CREATE PROCEDURE dbo.getpdinfo
@iPageCount int OUTPUT, --总页数
@iPage int, --当前页号
@iPageSize int --每页记录数
@Where nvarchar(1000)='' --查询条件
as
set nocount on
begin
--创建临时表
create table #t (ID int IDENTITY, --自增字段
ProductName nvarchar(40),
SupplierID int,
CategoryID int,
QuantityPerUnit nvarchar(20),
UnitPrice money)
--向临时表中写入数据
declare @vssql nvarchar(1200)
select @vssql='insert into #t select ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice from dbo.[Products]'+@Where
exec (@vssql)
--取得记录总数
declare @iRecordCount int
set @iRecordCount = @@rowcount --确定总页数
IF @iRecordCount%@iPageSize=0
SET @iPageCount=CEILING(@iRecordCount/@iPageSize)
ELSE
SET @iPageCount=CEILING(@iRecordCount/@iPageSize)+1
--若请求的页号大于总页数,则显示最后一页
IF @iPage > @iPageCount
SELECT @iPage = @iPageCount --确定当前页的始末记录
DECLARE @iStart int --start record
DECLARE @iEnd int --end record
SELECT @iStart = (@iPage - 1) * @iPageSize
SELECT @iEnd = @iStart + @iPageSize + 1 --取当前页记录
select * from #t where ID>@iStart and ID<@iEnd --删除临时表
DROP TABLE #t --返回记录总数
return @iRecordCount
end
GO
@Where nvarchar(1000)='' --查询条件
as
declare @sql nvarchar(1000)
set nocount on
begin
--创建临时表
create table #t (ID int IDENTITY, --自增字段
ProductName nvarchar(40),
SupplierID int,
CategoryID int,
QuantityPerUnit nvarchar(20),
UnitPrice money)
--向临时表中写入数据 set @sql = 'insert into #t
select ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice from dbo.[Products]'+@Where
end
go