Hi I'm getting an error when trying to run the stored proc , this is very urgent

[swenri1]

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]

 

[djj55]

Try 'tempdb..#Result' instead of '[dbo].[tempDB.#Result]'

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[yelworcm]

In fact, just #Result should be enough, as temp tables are always specific to a connection. As a matter of best practice, a procedure should drop any temp tables at the end, so that other instances of the stored procedure start with a clean slate.

 

[SQLBill]

I suggest always starting stored procedures with IF...EXISTS...DROP TABLE and drop any #temp tables that will be used by the stored procedure if they already exist. Why? Let's say your stored procedure fails after a temp table has been created. Then you try to rerun it, if you don't look for the existence of tables that you create the proc will fail if they already exist since it originally failed before the drop happens.

Meanwhile...remember that it is Database.Owner.Object

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[swenri1]

Thank you for the reply but, it didn't work.. Can any one help me please ?

 

[djj55]

So what did you try? Are you still getting the same error?

Did you straighten out the #Result references? Your [dbo].[tempDB.#Result] is wrong.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[SQLBill]

Since you don't seem to understand our suggestion, change this

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

to this

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

If you have done that, let us know what error you are getting now....since it would have to be a different error.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[pmurch]

Don't know if you've solved this, but I suggest a minor adjustment to SQLBill's reply...

IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(U'tempdb.dbo.#Result') AND type in ('U'))
DROP TABLE tempdb.dbo.#Result