Getting error in stored proc with Temp tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Getting error in stored proc with Temp tables

Author

Topic

swenri
Yak Posting Veteran

72 Posts

Posted - 2013-06-12 : 15:08:50

Hi,

I'm trying to run a stored proc with Temp tables and when I run the stored proc I'm getting an error as shown below. Can any body help me please? I have permissions for the database and Temp Database also. All help is highly appreciated.

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '[dbo].[tempDB.#Result]'.

Below is the code for the stored proc.

USE tempDB

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(U'[dbo].[tempDB.#Result]') AND type in (U'P', U'PC'))
DROP TABLE [dbo].[tempDB.#Result]
GO

USE [DatamartDB2]
GO

/****** Object: StoredProcedure [dbo].[MMC_SP_FoodNutritionOrdersReport1] Script Date: 11/29/2012 10:58:45 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MMC_SP_FoodNutritionOrdersReport_Test1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MMC_SP_FoodNutritionOrdersReport_Test1]
GO

USE [DatamartDB2]
GO

/****** Object: StoredProcedure [dbo].[MMC_SP_FoodNutritionOrdersReport_Test1] Script Date: 06/12/2013 13:03:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[MMC_SP_FoodNutritionOrdersReport_Test1]

AS

BEGIN

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

PRINT 'MMC_SP_FoodNutritionOrdersReport_Test1'
DECLARE @Location varchar(255)

--if (ISNULL(@Location,'')='')
begin

Declare @FromDate datetime
Declare @ToDate datetime
Declare @RptTimeFrameout VARCHAR(50)

SET @RptTimeFrameout='1' --Weekly
if @RptTimeFrameout='1' -- Daily
Begin
select @FromDate = CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,0,getdate()),101))+ '00:00:00 AM')
select @ToDate = CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'04:46:00 AM')

End

--DROP TABLE #Result

SELECT DISTINCT
("bllocation"."location_name") AS ROOM,
"MO_Times"."RegistrationAdmissionTime",
"MO_Demographics"."MRN",
("MO_Demographics"."LastName" + ' ' + "MO_Demographics"."FirstName") AS PatientsName,
"MO_Demographics"."Age",
("BLOrdersLog"."Text_str") + ' ' + ISNULL ("BLOrdersLog"."ForEveryText",'') AS Diet,
("BLOrdersLog"."OrderedTime") AS DietOrderedTime,
ISNULL("BLOrdersLog"."Annotation",'ND') AS Annotation,
isnull(BLUserFreeText.UserFreeText, BVProblems.Problem) as Allergy_Description,
@FromDate AS Fromdate,
@ToDate AS Todate into #Result
FROM ((((((("DatamartDB2"."dbo"."BLSession_Extended" "BLSession_Extended"
INNER JOIN "DatamartDB2"."dbo"."BLOrdersLog" "BLOrdersLog"
ON "BLSession_Extended"."sessionID"="BLOrdersLog"."sessionID"
AND "BLOrdersLog"."Text_str" LIKE '%Order NPO%'
AND "BLOrdersLog"."Text_str" IS NOT NULL
)
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Demographics" "MO_Demographics"
ON "BLSession_Extended"."sessionID"="MO_Demographics"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Times" "MO_Times"
ON "BLSession_Extended"."sessionID"="MO_Times"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVProblems" "BVProblems"
ON "BLSession_Extended"."sessionID"="BVProblems"."SessionID"

AND "BVProblems"."ObjectName" LIKE '%allerg%'
AND "BVProblems".Existence IN('exists','yes','suspected','rule out','ruled out'))
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVFindings" "BVFindings"
ON "BLSession_Extended"."sessionID"="BVFindings"."SessionID")

LEFT OUTER JOIN "DatamartDB2"."dbo"."BLUserFreeText" "BLUserFreeText"
ON ("BVProblems"."sessionid"="BLUserFreeText"."sessionID"
AND "BVProblems"."AtomID"="BLUserFreeText"."AtomID"
AND "BVProblems"."RowID" = "BLUserFreeText"."RowID"
AND "BVProblems"."ObjectName" LIKE '%allerg%'
AND "BVProblems".Existence IN('exists','yes'))

LEFT OUTER JOIN "DatamartDB2"."dbo"."BLPatient_Location" "BLPatient_Location"
ON "BLSession_Extended"."sessionID"="BLPatient_Location"."sessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."bllocation" "bllocation"
ON "BLPatient_Location"."location_num"="bllocation"."location_num")

WHERE

"BLSession_Extended"."FacilityID"=0
AND "BLSession_Extended"."status_num" <> 8000 -- Not Archived -added on 05-15-2013 sudha
AND "MO_Demographics"."MRN" NOT LIKE '%Test%'
--AND "MO_Demographics"."MRN" = '41579622'
AND "BLOrdersLog"."CancelledSig" IS NULL
AND "BLOrdersLog"."ORDERTYPE" = 0

AND BLPatient_Location.exit_time IS NULL
AND "MO_Times"."DischargeTime" IS NULL

UNION

SELECT DISTINCT

("bllocation"."location_name") AS ROOM,
"MO_Times"."RegistrationAdmissionTime",
"MO_Demographics"."MRN",
("MO_Demographics"."LastName" + ' ' + "MO_Demographics"."FirstName") AS PatientsName,
"MO_Demographics"."Age",

("BLOrdersLog"."Text_str") + ' ' + ISNULL ("BLOrdersLog"."ForEveryText",'') AS Diet,

("BLOrdersLog"."OrderedTime") AS DietOrderedTime,

ISNULL("BLOrdersLog"."Annotation",'ND') AS Annotation,

isnull(BLUserFreeText.UserFreeText, BVProblems.Problem) as Allergy_Description,
@FromDate AS Fromdate,
@ToDate AS Todate

FROM ((((((("DatamartDB2"."dbo"."BLSession_Extended" "BLSession_Extended"
INNER JOIN "DatamartDB2"."dbo"."BLOrdersLog" "BLOrdersLog"
ON "BLSession_Extended"."sessionID"="BLOrdersLog"."sessionID"
AND "BLOrdersLog"."Text_str" LIKE '%diet%'
AND "BLOrdersLog"."Text_str" IS NOT NULL
)
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Demographics" "MO_Demographics"
ON "BLSession_Extended"."sessionID"="MO_Demographics"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Times" "MO_Times"
ON "BLSession_Extended"."sessionID"="MO_Times"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVProblems" "BVProblems"
ON "BLSession_Extended"."sessionID"="BVProblems"."SessionID"

AND "BVProblems"."ObjectName" LIKE '%allerg%'
AND "BVProblems".Existence IN('exists','yes','suspected','rule out','ruled out')

)

LEFT OUTER JOIN "DatamartDB2"."dbo"."BVFindings" "BVFindings"
ON "BLSession_Extended"."sessionID"="BVFindings"."SessionID")

LEFT OUTER JOIN "DatamartDB2"."dbo"."BLUserFreeText" "BLUserFreeText"
ON ("BVProblems"."sessionid"="BLUserFreeText"."sessionID"
AND "BVProblems"."AtomID"="BLUserFreeText"."AtomID"
AND "BVProblems"."RowID" = "BLUserFreeText"."RowID"

AND "BVProblems"."ObjectName" LIKE '%allerg%'
AND "BVProblems".Existence IN('exists','yes')

)
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLPatient_Location" "BLPatient_Location"
ON "BLSession_Extended"."sessionID"="BLPatient_Location"."sessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."bllocation" "bllocation"
ON "BLPatient_Location"."location_num"="bllocation"."location_num")

WHERE

"BLSession_Extended"."FacilityID"=0
AND "BLSession_Extended"."status_num" <> 8000 -- Not Archived -added on 05-15-2013 sudha
AND "MO_Demographics"."MRN" NOT LIKE '%Test%'
--AND "MO_Demographics"."MRN" = '41579622'
AND "BLOrdersLog"."CancelledSig" IS NULL
AND "BLOrdersLog"."ORDERTYPE" = 0
AND BLPatient_Location.exit_time IS NULL
AND "MO_Times"."DischargeTime" IS NULL

UNION

SELECT DISTINCT
("bllocation"."location_name") AS ROOM,
"MO_Times"."RegistrationAdmissionTime",
"MO_Demographics"."MRN",
("MO_Demographics"."LastName" + ' ' + "MO_Demographics"."FirstName") AS PatientsName,
"MO_Demographics"."Age",

("BLOrdersLog"."Text_str") + ' ' + ISNULL ("BLOrdersLog"."ForEveryText",'') AS Diet,

("BLOrdersLog"."OrderedTime") AS DietOrderedTime,

ISNULL("BLOrdersLog"."Annotation",'ND') AS Annotation,

isnull(BLUserFreeText.UserFreeText, BVProblems.Problem) as Allergy_Description,

@FromDate AS Fromdate,
@ToDate AS Todate

FROM ((((((("DatamartDB2"."dbo"."BLSession_Extended" "BLSession_Extended"
INNER JOIN "DatamartDB2"."dbo"."BLOrdersLog" "BLOrdersLog"
ON "BLSession_Extended"."sessionID"="BLOrdersLog"."sessionID"
AND "BLOrdersLog"."Text_str" LIKE '%Order NPO + Ice Chips%'
AND "BLOrdersLog"."Text_str" IS NOT NULL)
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Demographics" "MO_Demographics"
ON "BLSession_Extended"."sessionID"="MO_Demographics"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Times" "MO_Times"
ON "BLSession_Extended"."sessionID"="MO_Times"."SessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVProblems" "BVProblems"
ON "BLSession_Extended"."sessionID"="BVProblems"."SessionID"

AND "BVProblems"."ObjectName" LIKE '%allerg%'
AND "BVProblems".Existence IN('exists','yes','suspected','rule out','ruled out')
)
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVFindings" "BVFindings"
ON "BLSession_Extended"."sessionID"="BVFindings"."SessionID")

LEFT OUTER JOIN "DatamartDB2"."dbo"."BLUserFreeText" "BLUserFreeText"
ON ("BVProblems"."sessionid"="BLUserFreeText"."sessionID"
AND "BVProblems"."AtomID"="BLUserFreeText"."AtomID"
AND "BVProblems"."RowID" = "BLUserFreeText"."RowID"

AND "BVProblems"."ObjectName" LIKE '%allerg%'
AND "BVProblems".Existence IN('exists','yes'))
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLPatient_Location" "BLPatient_Location"
ON "BLSession_Extended"."sessionID"="BLPatient_Location"."sessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."bllocation" "bllocation"
ON "BLPatient_Location"."location_num"="bllocation"."location_num")

WHERE
"BLSession_Extended"."FacilityID"=0
AND "BLSession_Extended"."status_num" <> 8000 -- Not Archived -added on 05-15-2013 sudha
AND "BLOrdersLog"."CancelledSig" IS NULL
AND "BLOrdersLog"."ORDERTYPE" = 0
AND "MO_Demographics"."MRN" NOT LIKE '%Test%'

AND BLPatient_Location.exit_time IS NULL
AND "MO_Times"."DischargeTime" IS NULL

ORDER BY
"bllocation"."location_name" ASC

select rlt1.*
from #result rlt1
where dietorderedtime in
(select top 1 dietorderedtime
from #result rlt2
where rlt1.mrn=rlt2.mrn
order by dietorderedtime desc)

END

END

SET NOCOUNT OFF

SET ANSI_NULLS OFF

GO

GO
GRANT EXECUTE ON [dbo].[MMC_SP_FoodNutritionOrdersReport_Test1] TO [Public]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-12 : 18:16:32

Looks like the problem is in this statement:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(U'[dbo].[tempDB.#Result]') AND type in (U'P', U'PC'))
DROP TABLE [dbo].[tempDB.#Result]
GO

If you are trying to create a temp table in your database under dbo schema, use the following:

IF object_id('tempdb.dbo.#Result') IS NOT NULL DROP TABLE #Result;

swenri
Yak Posting Veteran

72 Posts

Posted - 2013-06-13 : 10:24:26

Thank you for teh reply. But, It didn't work .. Can any body help me please ?

swenri
Yak Posting Veteran

72 Posts

Posted - 2013-06-13 : 10:24:32

Thank you for teh reply. But, It didn't work .. Can any body help me please ?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-13 : 10:43:12

quote:
Originally posted by swenri

Thank you for teh reply. But, It didn't work .. Can any body help me please ?

What is it doing? Is it running without any errors, but giving no results, or is it giving syntax errors? If it is giving syntax errors, post the exact text of the error message.

You can double-click on the error message and it will take you to the line where parser is seeing the error. That will help you narrow down the segement of code that you have to fix.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources