Where is the ERROR in my procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Where is the ERROR in my procedure?

Author

Topic

amgrace
Starting Member

30 Posts

Posted - 2004-06-04 : 10:33:02

I am trying to create a stored procedure and I keep getting the error below:

Server: Msg 156, Level 15, State 1, Procedure UCLFindDuplicateRegistrations, Line 7
Incorrect syntax near the keyword 'VIEW'.
Server: Msg 156, Level 15, State 1, Procedure UCLFindDuplicateRegistrations, Line 24
Incorrect syntax near the keyword 'VIEW'.

Can anyone spot this errors please.....I thought my syntax was correct. Thanks

CREATE PROCEDURE UCLFindDuplicateRegistrations
AS

---Alters View UCLGetMonthlyDataView1 to reflect new monthly data.
---Data is checked 3 months in arrears i.e If ran in JUNE, this query only selects data with EpisodeEndDate of MARCH)

ALTER VIEW UCLGetMonthlyDataView1
AS

SELECT DISTINCT
NHSNumber,
LocalPatientIdentifier as PatientID
FROM
Encounter
WHERE
NHSNumber is not null
AND
datediff(mm,EpisodeEndDate ,getdate()) = 3

---Alters View UCLGetMonthlyDataView2 to reflect new monthly data (view is identical to UCLGetMonthlyDataView1)
ALTER VIEW UCLGetMonthlyData2View
AS

SELECT DISTINCT
NHSNumber,
LocalPatientIdentifier as PatientID
FROM
Encounter
WHERE
NHSNumber is not null
AND
datediff(mm,EpisodeEndDate ,getdate()) = 3

-------Compares/Filters data in both Views to find Duplicate NHSNOs with different patientIDs
SELECT DISTINCT a.*
FROM UUCLGetMonthlyData1View a

INNER JOIN UCLGetMonthlyData2View b
ON
a.NHSNumber = b.NHSNumber

WHERE
a.PatientID<>b.PatientID

ORDER BY a.NHSNumber

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-04 : 11:10:21

You need a GO in between....

aldo try double clicking you the error message...it'll take you to the spot of the error in the code...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-04 : 11:12:23

Hold the phone...

You're trying to create a sproc that is an alter of a view?

You need dynamic sql...but why bother?

Brett

8-)

amgrace
Starting Member

30 Posts

Posted - 2004-06-04 : 11:22:40

I need to alter the views first (refresh it with the latest data) before comparing the data in both views and getting out the result

amgrace
Starting Member

30 Posts

Posted - 2004-06-04 : 11:53:36

I know where the problem is now......but how do I fix this. Do I need to enclose my "Alter View" statments in an "Exec"?

HELP!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources