CREATE PROC procName @currentPage int, @searchCondition varchar(500), @pageSize int, @recordCount int OUTPUT, @pageCount int OUTPUT AS IF EXISTS (SELECT name FROM sysobjects WHERE name = N'procName' AND type = 'P') DROP PROCEDURE procName SET NOCOUNT ON DECLARE @start int DECLARE @end int DECLARE @number int
SET @start = (@currentPage-1) * @pageSize SET @end = @start + @pageSize - 1
DECLARE @tableName table ( recordID int identity(1,1), --docID numeric url varchar(255), title varchar(255), abstract varchar(400), processtime datetime --pangRank varchar(50) ) --insert into @tableName(url,title,abstract,processtime) (select top 100 url,title,abstract,processtime from indexmain) --insert into @tableName(url,title,abstract,processtime) exec('insert into '+ @tableName+'(url,title,abstract,processtime)'+ @searchCondition) select @recordCount = @@ROWCOUNT --总记录条数 set @number = @recordCount % @pageSize if(@number = 0) set @pageCount = @number else set @pageCount = @number + 1 --return the results to the caller --select * from @tableName where recordID between @start and @end order by recordID GOexec procName 2,'select top 1 url,title,abstract,processtime from indexmain',10,5,5服务器: 消息 137,级别 15,状态 2,过程 procName,行 35 必须声明变量 '@tableName'。帮忙看看为什么
其余的地方都没有问题,就是insert into 哪个地方出错
简单看了你的存储过程,问题多多,感觉根本不用创建 表, 至于@tableName,你需要追加参数 @tableName varchar(100) 或在存储过程中增加定义:declare @tableName varchar(100) 还有IF EXISTS (SELECT name FROM sysobjects WHERE name = N'procName' AND type = 'P') DROP PROCEDURE procName 应该放在CREATE PROCEDURE 之前
按照你的逻辑改如下: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[procName] GOCREATE PROC procName @currentPage int, @searchCondition varchar(500), @pageSize int, @recordCount int OUTPUT, @pageCount int OUTPUT AS SET NOCOUNT ON DECLARE @start int DECLARE @end int DECLARE @number int
SET @start = (@currentPage-1) * @pageSize SET @end = @start + @pageSize - 1
select top 100 url,title,abstract,processtime into #temptable from indexmain select @recordCount = @@ROWCOUNT --总记录条数 select @number = @recordCount % @pageSize if(@number = 0) set @pageCount = @number else set @pageCount = @number + 1 --return the results to the caller select url,title,abstract,processtime from indexmain where recordID between @start and @end and recordID in ( select recordID from #temptable) order by recordID
select @sql = 'insert into ' +@tableName+'(url,title,abstract,processtime) @searchCondition'exec (@sql)
@currentPage int,
@searchCondition varchar(500),
@pageSize int,
@recordCount int OUTPUT,
@pageCount int OUTPUT
AS
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'procName'
AND type = 'P')
DROP PROCEDURE procName SET NOCOUNT ON
DECLARE @start int
DECLARE @end int
DECLARE @number int
SET @start = (@currentPage-1) * @pageSize
SET @end = @start + @pageSize - 1
DECLARE @tableName table
(
recordID int identity(1,1),
--docID numeric
url varchar(255),
title varchar(255),
abstract varchar(400),
processtime datetime
--pangRank varchar(50)
)
--insert into @tableName(url,title,abstract,processtime) (select top 100 url,title,abstract,processtime from indexmain)
--insert into @tableName(url,title,abstract,processtime)
exec('insert into '+ @tableName+'(url,title,abstract,processtime)'+ @searchCondition)
select @recordCount = @@ROWCOUNT --总记录条数
set @number = @recordCount % @pageSize
if(@number = 0)
set @pageCount = @number
else
set @pageCount = @number + 1 --return the results to the caller
--select * from @tableName where recordID between @start and @end order by recordID
GOexec procName 2,'select top 1 url,title,abstract,processtime from indexmain',10,5,5服务器: 消息 137,级别 15,状态 2,过程 procName,行 35
必须声明变量 '@tableName'。帮忙看看为什么
至于@tableName,你需要追加参数 @tableName varchar(100)
或在存储过程中增加定义:declare @tableName varchar(100) 还有IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'procName'
AND type = 'P')
DROP PROCEDURE procName
应该放在CREATE PROCEDURE 之前
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procName]
GOCREATE PROC procName
@currentPage int,
@searchCondition varchar(500),
@pageSize int,
@recordCount int OUTPUT,
@pageCount int OUTPUT
AS SET NOCOUNT ON
DECLARE @start int
DECLARE @end int
DECLARE @number int
SET @start = (@currentPage-1) * @pageSize
SET @end = @start + @pageSize - 1
select top 100 url,title,abstract,processtime into #temptable from indexmain
select @recordCount = @@ROWCOUNT --总记录条数 select @number = @recordCount % @pageSize
if(@number = 0)
set @pageCount = @number
else
set @pageCount = @number + 1 --return the results to the caller
select url,title,abstract,processtime from indexmain
where recordID between @start and @end and recordID in ( select recordID from #temptable)
order by recordID
GO