判断临时表是否存在
Way 1
if(exists(select name from tempdb..sysobjects where name like'%temptab%' and type='U'))
drop table #temptab
Way 2
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tempcitys') and type='U')
drop table #tempcitys
Way 3
IF OBJECT_ID('tempdb..#') IS NOT NULL
DROP TABLE #
OBJECT_ID此函数返回数据库对象标识号
判断数据库里有没有存在PerPersonData这样一张表
if exists (select * from sysobjects where objectproperty(object_id('PerPersonData'),'istable') = 1)
OBJECTPROPERTY：返回当前数据库中对象的有关信息。1表“真”。同样可以写成OBJECTPROPERTY(id, isUserTable) = 1
if exists (select * from sysobjects where id = object_id(N'PerPersonData') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table 'PerPersonData'
判断试图是否存在
if exists (select * from sysobjects where id = object_id(N‘[dbo].[ESTMP]‘)
and OBJECTPROPERTY(id, N‘IsView‘) = 1)
drop view ESTMP
案例：
/*
查询非优质案源
N'tempdb..#tempcitys'
If Object_Id( N'tempdb..#temptest') Is Not NULL--#Test 为临时表名
Begin
print '存在 '
End
Else
Begin
print '不存在 '
End
*/
If Object_Id( N'tempdb..#temptest') Is Not NULL --#temptest 为临时表名
Begin
select * from Tempdb.dbo.#temptest
where
--and SUBSTRING(CONVERT(VARCHAR, ws0116,120),1,10) >= '2016/01/01'
--and SUBSTRING(CONVERT(VARCHAR, ws0116,120),1,10) <= '2016/01/31'
and
(
房源描述<=50
or
出外网标题< 20
or
(roomImg<3 or roomImg is null)
or
(HouseShapeImg<=0 or HouseShapeImg is null)
)
order by ws0119
End
Else
Begin
--创建临时表
select ws0119,ws0102,sum(CASE df5003 WHEN '0002' THEN df5004 END) roomImg , --室内图数量
SUM(CASE df5003 WHEN '0000' THEN df5004 END) HouseShapeImg --房型图数量
,len(sa7319) as '出外网标题',sa7339 as '房源描述'
,ws0101,ws0131, ws0103,ws0160, TEMP.qubu, TEMP.pr0102, ws0135,
TEMP.pr0114, sa7319, na0105 ,ws0116
into Tempdb.dbo.#temptest
from WEB..WS001F
left join WEB..SA073F on sa7301 = ws0102
left join SINYI..DF050F on ws0101=df5002
inner join
( SELECT AA.pr0214,AA.pr0202, a.pr0102,a.pr0114 , b.pr0102 AS qubu,a.pr0101 FROM WEB..PR001F a
WITH (NOLOCK) INNER JOIN WEB..PR001F b WITH(NOLOCK) ON a.pr0113=b.pr0101 inner join WEB..PR002F AA
on a.pr0101=AA.pr0203 WHERE a.pr0135='0' AND a.pr0111 in('A1','B0') AND a.pr0104='1' AND a.pr0133='6'
and AA.pr0210='1'
) TEMP on ws0119 = TEMP.pr0202 and ws0160 = TEMP.pr0114
left join WEB..NA001F on na0101 = ws0102
--
where ws0126 = '1' and ws0104 = '0' and ws0131 in ('1','0','2','3')
group by
ws0101,ws0102,sa7339,sa7319
,ws0131, ws0119,ws0103,ws0160, TEMP.qubu, TEMP.pr0102, ws0135,
TEMP.pr0114, na0105,ws0116
---查询非优质案源
select * from Tempdb.dbo.#temptest
where
--and SUBSTRING(CONVERT(VARCHAR, ws0116,120),1,10) >= '2016/01/01'
--and SUBSTRING(CONVERT(VARCHAR, ws0116,120),1,10) <= '2016/01/31'
and
(
房源描述<=50
or
出外网标题< 20
or
(roomImg<3 or roomImg is null)
or
(HouseShapeImg<=0 or HouseShapeImg is null)
)
order by ws0119
End