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;
Go to Top of Page

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 ?
Go to Top of Page

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 ?
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -