临时表与分页 已经判断了为什么还

huerreson 2006-11-24 04:18:48
已经判断了为什么还是会报错:
数据库中已存在名为 '##Business_TempTb' 的对象。



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Business_Page]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_Business_Page]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/*
名称:sp_Business_Page
作用:商业前台分页
创建:2006-11-24 14:42
修改:
*/
CREATE PROCEDURE dbo.sp_Business_Page
(
@GetFields VARCHAR(1000) = '*',--默认取所有列
@OrderField NVARCHAR(50) = 'ID',--默认以ID排序
@PageSize INT = 1000,--默认每页显示10条
@PageIndex INT = 1,--默认当前页为1
@DoCount BIT = 1,--默认执行统计
@OrderType BIT = 1,--默认倒序排列
@StrWhere VARCHAR(1500) = '',--默认查询条件为空
@RowCount INT = NULL OUTPUT --输出总记录数
)
AS
--------------------------以下重排后创建全局临时表:##Business_TempTb
DECLARE @SqlStr VARCHAR(5000)
IF @StrWhere != ''
BEGIN
SET @SqlStr = N'select id = identity(int,1,1),UserName,BTypeID,BNTypeID,BTitle,BKeyword,BValidate,BPostDate,BPic,BHPic,LanguageID,BCompanyName,BCountryID,BProvinceID,BCity,userType,BRecommend,Substring(BContent,1,50) as BContent,MyIM,BState,BOffline,EffectivePeriod,SupCatID INTO ##Business_TempTb from business where ' + @StrWhere + ' order by usertype desc,BPostDate desc'
END
ELSE
BEGIN
SET @SqlStr = N'select id = identity(int,1,1),UserName,BTypeID,BNTypeID,BTitle,BKeyword,BValidate,BPostDate,BPic,BHPic,LanguageID,BCompanyName,BCountryID,BProvinceID,BCity,userType,BRecommend,Substring(BContent,1,50) as BContent,MyIM,BState,BOffline,EffectivePeriod,SupCatID INTO ##Business_TempTb from business order by usertype desc,BPostDate desc'
END
IF OBJECT_ID('tempdb.##Business_TempTb') IS NOT NULL
DROP TABLE ##Business_TempTb
EXECUTE (@SqlStr)
---------------------------以下执行分页过程
DECLARE @strSQL VARCHAR(5000)
DECLARE @strTmp VARCHAR(110)
DECLARE @strOrder VARCHAR(400)
DECLARE @strSQL1 NVARCHAR(4000)
DECLARE @intRootRecordCount INT
IF @doCount != 0
BEGIN
IF @strWhere !=''
SET @strSQL1 = 'set nocount on;select @SPintRootRecordCount = count(*) from [##Business_TempTb] where '+@strWhere
ELSE
SET @strSQL1 = 'set nocount on;select @SPintRootRecordCount = count(*) from [##Business_TempTb]'
EXECUTE sp_executesql @strSQL1,N'@SPintRootRecordCount int OUTPUT',@SPintRootRecordCount = @intRootRecordCount OUTPUT
SELECT @rowcount = @intRootRecordCount
END
ELSE
SELECT @rowcount = 0
BEGIN
IF @OrderType != 0
BEGIN
SET @strTmp = '<(select min'
SET @strOrder = ' order by [' + @orderField +'] desc'
END
ELSE
BEGIN
SET @strTmp = '>(select max'
SET @strOrder = ' order by [' + @orderField +'] asc'
END
IF @PageIndex = 1
BEGIN
IF @strWhere != ''
SET @strSQL = 'select top ' + str(@PageSize) +' '+@GetFields+ ' from [##Business_TempTb] where ' + @strWhere + ' ' + @strOrder
ELSE
SET @strSQL = 'select top ' + str(@PageSize) +' '+@GetFields+ ' from [##Business_TempTb] '+ @strOrder
END
ELSE
BEGIN
SET @strSQL = 'select top ' + STR(@PageSize) +' '+@GetFields+ ' from [##Business_TempTb] where [' + @orderField + ']' + @strTmp + '(['+ @orderField + ']) from (select top ' + STR((@PageIndex-1)*@PageSize) + ' ['+ @orderField + '] from [##Business_TempTb]' + @strOrder + ') as tblTmp)'+ @strOrder
IF @strWhere != ''
SET @strSQL = 'select top ' + STR(@PageSize) +' '+@GetFields+ ' from [##Business_TempTb] where [' + @orderField + ']' + @strTmp + '(['
+ @orderField + ']) from (select top ' + STR((@PageIndex-1)*@PageSize) + ' ['
+ @orderField + '] from [##Business_TempTb] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
END
END
EXECUTE (@strSQL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

...全文
106 4 打赏 收藏 转发到动态 举报
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
oooooo126 2006-11-24
  • 打赏
  • 举报
回复
if exists (select * from sys.objects where object_id = object_id(N'[dbo].[sp_Business_Page]')
小毛病真多
oooooo126 2006-11-24
  • 打赏
  • 举报
回复
if exists (select * from dbo.sysobjects where object_id = object_id(N'[dbo].[sp_Business_Page]')
dulei115 2006-11-24
  • 打赏
  • 举报
回复
[ database_name.[ owner ] .| owner.] table_name
即有下面四种形式,tempdb是database_name,应该用1,2,而不是3
1.database_name.owner.table_name
2.database_name..table_name
3.owner.table_name
4.table_name
dulei115 2006-11-24
  • 打赏
  • 举报
回复
tempdb..##Business_TempTb --两点

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧