sql - 创建存储过程时对象名称无效

Question

我正在尝试在 SQL Server 2008 上创建一个存储过程。每当我尝试执行该过程时，我都会在 #EDPTS 表上收到无效的对象名称错误。有人可以看看下面的代码让我知道可能是什么造成了这个问题吗？提前谢谢你

use maindb
go 
CREATE PROCEDURE DailyStats
AS

set transaction isolation level read uncommitted

DECLARE @STARTDATE DATE, @ENDDATE DATE
SET @ENDDATE = CAST(GETDATE() AS DATE)
SET @STARTDATE = DATEADD(DAY,-1, @ENDDATE)


if OBJECT_ID('tempdb..#EDPTS')is not null
drop table #EDPTS

SELECT DISTINCT PV.[EID]
  ,ENC.PERSON_ID
  ,PV.[VisitNumber]
  ,ENC.MRN
  ,PER.NAME_FULL_FORMATTED as 'PTName'
  ,PER.SEX_CD_DESCR as 'Gender'
  ,CONVERT(varchar,PER.BIRTH_DT_TM,101)as 'DOB'
  ,DATEDIFF (YEAR,PER.BIRTH_DT_TM,ENC.ARRIVE_DT_TM)as 'ArrivalAge'
  ,MIN (CHIN.CHECKIN_DT_TM) as 'EDArrival'
  ,ENC.ADMIT_MODE_CD_DESCR
  ,ENC.REASON_FOR_VISIT as 'ChiefComp'
  ,[PatientClass]
  ,ACCOMMODATION_CD_DESCR as 'Accommodation'
  ,[AssignedPatientLocationFacility]
  ,[AdmitSourceDescr]
  ,[FinancialClass]
  ,CHIN.CHECKOUT_DISPOSITION_CD_DESCR as 'DCDispo'
  ,CASE WHEN MAX (CHIN.CHECKOUT_DT_TM) = '2100-12-31 00:00:00.000' then NULL ELSE MAX         (CHIN.CHECKOUT_DT_TM)END as 'EDDepart'
  ,CASE WHEN DATEDIFF (minute,MIN(CHIN.CHECKIN_DT_TM),MAX(CHIN.CHECKOUT_DT_TM))> '1440'  then NULL ELSE
   DATEDIFF (minute,MIN(CHIN.CHECKIN_DT_TM),MAX(CHIN.CHECKOUT_DT_TM))END as 'EDLOS'
INTO #EDPTS
--finds ed pts
FROM tbl.one
with (nolock)
--more ed data
INNER JOIN tbl.two
with (nolock)
on PV.EID = ENC.EID 
--finds demographics
INNER JOIN tbl.three
with (nolock)
on ENC.PERSON_ID = PER.PERSON_ID
--finds ed discharge
LEFT Join tbl.four
with (nolock)
on ENC.EID = CHIN.EID


WHERE AdmissionType = '1'
and CHIN.CHECKIN_DT_TM between @STARTDATE and @ENDDATE
and AccountStatus <> 'CANCELLED
and chin.CHECKOUT_DISPOSITION_CD_DESCR <> 'Duplicate/Accidental Registration'
and enc.ENCNTR_CLASS_CD_DESCR <> 'Preadmit'
and ENC.ENCNTR_CLASS_CD_DESCR <> 'Limited Stay


GROUP BY PV.[EID]
  ,ENC.PERSON_ID
  ,PV.[VisitNumber]
  ,ENC.MRN
  ,PER.NAME_FULL_FORMATTED 
  ,PER.SEX_CD_DESCR
  ,CONVERT(varchar,PER.BIRTH_DT_TM,101)
  ,DATEDIFF (YEAR,PER.BIRTH_DT_TM,ENC.ARRIVE_DT_TM)
  ,ENC.ADMIT_MODE_CD_DESCR
  ,ENC.REASON_FOR_VISIT
  ,[PatientClass]
  ,ACCOMMODATION_CD_DESCR
  ,[AssignedPatientLocationFacility]
  ,[AdmitSourceDescr]
  ,[FinancialClass]
  ,CHECKOUT_DISPOSITION_CD_DESCR 

GO

if OBJECT_ID('tempdb..#Tri')is not null
drop table #Tri

SELECT  EVE.EID 
    ,MIN (CE.PERFORMED_DT_TM)as 'TriStart'
    ,MIN (EVE.COMPLETE_DT_TM) as 'TriComp'
    ,MAX(PRSNL.NAME_FULL_FORMATTED)  as 'TriNurse'

INTO #Tri
from tbl.one


GO

if OBJECT_ID('tempdb..#Tri2')is not null
drop table #Tri2

SELECT CE.EID 
  ,MAX(CE.RESULT_VAL) as 'Acuity

GROUP BY CE.EID 
GO

if OBJECT_ID('tempdb..#EDBed')is not null
drop table #EDBed

if OBJECT_ID('tempdb..#MDtoSee')is not null
drop table #MDtoSee

SELECT EVE.EID
  ,MIN (EVE.COMPLETE_DT_TM) as 'MDtoSee
SELECT #EDPTS.EID 
   ,#EDPTS.PERSON_ID 
   ,#EDPTS.VisitNumber 
   ,#EDPTS.AssignedPatientLocationFacility 
   ,#EDPTS.MRN
   ,#EDPTS.PTName
   ,#EDPTS.Gender
   ,#EDPTS.DOB 
   ,#EDPTS.ArrivalAge 
   ,MIN(#EDPTS.EDArrival)as EDArrival
   ,#EDPTS.ADMIT_MODE_CD_DESCR
   ,#EDPTS.ChiefComp 
   ,#EDPTS.AdmitSourceDescr
   ,#Tri2.Acuity
   ,#Tri.TriStart 
   ,#Tri.TriComp 
   ,DATEDIFF (minute,#EDPTS.EDArrival,#Tri.TriStart)
   ,DATEDIFF (minute,#Tri.TriStart,#Tri.TriComp)
   ,#Tri.TriNurse 
   ,#EDBed.EDBed 
   ,DATEDIFF (minute,#EDPTS.EDArrival,#EDBed.EDBed)
   ,#MDtoSee.MDtoSee 
   ,DATEDIFF (minute,#EDPTS.EDArrival,#MDtoSee.MDtoSee)
   ,#EDPTS.PatientClass 
   ,#EDPTS.FinancialClass 
   ,#EDPTS.Accommodation 
   ,#EDPTS.DCDispo 
   ,MAX(#EDPTS.EDDepart)as EDDepart
   ,#EDPTS.EDLOS 

 FROM #EDPTS with (nolock)LEFT JOIN #Tri with (nolock) on #EDPTS.EID = #Tri.EID 
 LEFT JOIN #Tri2 with (nolock) on #EDPTS.EID = #Tri2.EID 
 LEFT JOIN #EDBed with (nolock) on #EDPTS.EID = #EDBed.EID
 LEFT JOIN #MDtoSee with (nolock) on #EDPTS.EID = #MDtoSee.EID 
 GROUP BY #EDPTS.EID 
   ,#EDPTS.PERSON_ID 
   ,#EDPTS.VisitNumber 
   ,#EDPTS.AssignedPatientLocationFacility 
   ,#EDPTS.MRN
   ,#EDPTS.PTName
   ,#EDPTS.Gender
   ,#EDPTS.DOB 
   ,#EDPTS.ArrivalAge 
   ,#EDPTS.ADMIT_MODE_CD_DESCR
   ,#EDPTS.ChiefComp 
   ,#EDPTS.AdmitSourceDescr
   ,#Tri2.Acuity
   ,#Tri.TriStart 
   ,#Tri.TriComp 
   ,DATEDIFF (minute,#EDPTS.EDArrival,#Tri.TriStart)
   ,DATEDIFF (minute,#Tri.TriStart,#Tri.TriComp)
   ,#Tri.TriNurse 
   ,#EDBed.EDBed 

Answer 1

GO 导致了问题。它正在结束包含 CREATE PROCEDURE 的批处理。这是正在发生的简化情况。

IF OBJECT_ID('Test1') IS NOT NULL
DROP PROCEDURE test1

GO -- End batch

CREATE PROCEDURE Test1 
AS 
    IF Object_id('tempdb.dbo.#temp') IS NOT NULL 
      DROP TABLE #temp

    SELECT 1 AS test 
    INTO   #temp

GO -- Ends the CREATE PROCEDURE batch

SELECT * 
FROM   #temp