ALTER proc sp_Web_tbGoldOrderSearch
@startDate datetime,
@endDate datetime,
@gameName varchar(50),
@vcSerialNo varchar(18),
@vcCustActorName varchar(30),
@vcCustID varchar(18)
as
declare @snGameID int
begin
begin
if object_id('#TempGoldOrder') is not null drop table #TempGoldOrder
end
这样为什么不能删除 #TempGoldOrder 这个临时表呢?
该怎么写呢?
@startDate datetime,
@endDate datetime,
@gameName varchar(50),
@vcSerialNo varchar(18),
@vcCustActorName varchar(30),
@vcCustID varchar(18)
as
declare @snGameID int
begin
begin
if object_id('#TempGoldOrder') is not null drop table #TempGoldOrder
end
这样为什么不能删除 #TempGoldOrder 这个临时表呢?
该怎么写呢?
-- Author :SQL77(只为思齐老)
-- Date :2010-01-20 14:32:01
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([ID] int,[ContractID] int,[Amount] int)
insert #TB
select 1,1,10000 union all
select 2,12,20000 union all
select 3,123,30000
--> 测试数据:#TB1
if object_id('tempdb.dbo.#TB1') is not null drop table #TB1
go
create table #TB1([ID] int,[ContractID] int,[GetAmout] int)
insert #TB1
select 1,1,1000 union all
select 2,1,2000 union all
select 2,12,3000 union all
select 3,12,4000
--------------开始查询--------------------------
select SUM(GetAmout)GetAmout from #TB1 T WHERE EXISTS(SELECT 1 FROM #TB WHERE ContractID=T.ContractID)
--GROUP BY ContractID
----------------结果----------------------------
/*
(所影响的行数为 3 行)
(所影响的行数为 4 行)GetAmout
-----------
10000(所影响的行数为 1 行)
*/
go
如果在TEMPDB库执行的 就不需要加TEMPDB..
可以用drop #临时表