This Stored procedure is not droping other SP

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

General SQL Server Forums

New to SQL Server Programming

This Stored procedure is not droping other SP

Author

Topic

kshahzad
Starting Member

45 Posts

Posted - 2013-07-04 : 19:05:21

I am getting this error message when i run this Stored Procedure

Error Msg:
Msg 2714, Level 16, State 3, Procedure NIC_OA_GetPhysicianXRayReportsOut, Line 119
There is already an object named 'NIC_OA_GetPhysicianXRayReportsOut' in the database

IF

EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NIC_OA_GetPatientXRayReportsOut]') AND type in (N'P', N'PC'))

DROP

PROCEDURE [dbo].[NIC_OA_GetPatientXRayReportsOut]

GO

/****** Object: StoredProcedure [dbo].[NIC_OA_GetPatientXRayReportsOut] Script Date: 03/27/2012 18:18:31 ******/

SET

ANSI_NULLS ON

GO

SET

QUOTED_IDENTIFIER ON

GO

' Execution Samples: NIC_OA_GetPhysicianXRayReportsOut @lPhysician

' NIC_OA_GetPhysicianXRayReportsOut 70

'

'

' REVISION HISTORY

'

' Date Developer Comments

' _________ __________________ _____________________________________________

'

' 12/22/2009 Klew #38760 - Added filter to return results that have not been removed. (bReportRemoved=0).

' 04/08/2010 Klew Added "AND bReportRemoved =0"

' 77234 Roger Nie DI form tests were not show up in list

' 77234 Roger Nie optimized based on Sarjen's comment

' 27Mar2012 Kane case 91451, open eForm requisition

' 03Jul2013 Kane case 122442 - add paging feature to DI requisitions

'******************************************************************************/

create

PROCEDURE [dbo].[NIC_OA_GetPhysicianXRayReportsOut] @lPhysician INT,

@Page

INT=1,

@RecsPerPage

INT=50

AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SET NOCOUNT ON

CREATE TABLE #TempItems

(

ID

INT IDENTITY,

lID

INT NOT NULL

)

INSERT INTO #TempItems

(lID)

--SELECT lID FROM tblItem

SELECT a.lid

FROM [PhysicianXRayRequisitions] a

INNER JOIN [Map_XRayRequisitionToProgressNote] b

ON a.lID = b.lXRayRequisition

INNER JOIN [ProgressNote] c

ON c.lID = b.lProgressNote

INNER JOIN patient d

ON c.lPatient = d.lID

WHERE a.lPhysician = @lPhysician

AND a.nRecordStatus = 1

AND a.bReportReceived = 0

AND a.bReportRemoved = 0

AND a.szLastPrintedBy IS NOT NULL

ORDER BY a.dDateOrdered ASC

-- Find out the first and last record we want

DECLARE @FirstRec INT,

@LastRec

INT

SELECT @FirstRec = ( @Page - 1 ) * @RecsPerPage

SELECT @LastRec = ( @Page * @RecsPerPage + 1 )

SELECT Isnull(b.szLaboratory, 'non selected') AS szLaboratory,

a

.lID,

a

.bDiagnosticMammogram,

a

.bScreeningMammogram,

a

.dDateOrdered,

a

.dCheckForReport,

p

.szLast,

p

.szFirst,

a

.lPhysician,

p

.szFirst AS szPhysicianFirst,

p

.szLast AS szPhysicianLast,

szLastPrintedBy

,

pat

.szFirst AS szPatFirst,

pat

.szLast AS szPatLast,

pat

.lid AS lPatient,

Rtrim(Isnull(DIform.szFileName, '')) AS szDIFormFileName,

a

.lDIform_data,

DIform_data

.lDIform,

a

.szListRequisition_FreeForm,

Isnull(f.FormLiteFormID, 0) AS FormLiteFormID,

f

.FormLiteSnapshotID,

TotalRecords

= (SELECT Count(*) FROM #TempItems TI)

FROM #TempItems

Inner join [PhysicianXrayRequisitions] a

ON a.lid = #TempItems.lID

LEFT JOIN [Laboratory] b

ON b.lID = a.lLaboratory

LEFT JOIN FormLiteSnapshotMap f

ON a.lid = f.lPhysicianXRayRequisitions

INNER JOIN [Map_XRayRequisitionToProgressNote] c

ON c.lXRayRequisition = a.lID

INNER JOIN [ProgressNote] d

ON c.lProgressNote = d.lID

INNER JOIN [Patient] pat

ON d.lPatient = pat.lID

INNER JOIN [Physician] p

ON p.lid = a.lPhysician

LEFT JOIN DIform_data

ON DIform_data.lid = a.lDIform_data

LEFT JOIN DIform

ON DIform.lid = DIform_data.lDIform

WHERE #TempItems.ID > @FirstRec

AND #TempItems.ID < @LastRec

ORDER BY #TempItems.ID

SET NOCOUNT OFF

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-07-05 : 00:56:44

You are dropping and creating diff procedure name


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NIC_OA_GetPatientXRayReportsOut]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[NIC_OA_GetPatientXRayReportsOut]

create PROCEDURE [dbo].[NIC_OA_GetPhysicianXRayReportsOut] @lPhysician INT,

KH
[spoiler]Time is always against us[/spoiler]

kshahzad
Starting Member

45 Posts

Posted - 2013-07-05 : 08:03:35

Thanks KH, appriciate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources