SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Getting error in stored proc with Temp tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

swenri
Yak Posting Veteran

72 Posts

Posted - 06/12/2013 :  15:08:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 06/12/2013 :  18:16:32  Show Profile  Reply with Quote
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 - 06/13/2013 :  10:24:26  Show Profile  Reply with Quote
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 - 06/13/2013 :  10:24:32  Show Profile  Reply with Quote
Thank you for teh reply. But, It didn't work .. Can any body help me please ?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 06/13/2013 :  10:43:12  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000