Jump to content

stored procedure and join


demuro1
 Share

Recommended Posts

Hello, I am taking a class on sql. We are using sql server 2008 r2. We have a project to create a database and create 3 stored procedures. I have created the database as well as the entries for it. The stored procedures are giving me a little trouble though. I'd love if you could offer some help on how to fix these. Thanks so much! I have 2 that I am working on: stored procedure 1 returns these errors:

Msg 102, Level 15, State 1, Procedure sp_todaysappt, Line 3Incorrect syntax near '('.Msg 137, Level 15, State 2, Procedure sp_todaysappt, Line 13Must declare the scalar variable "@date".Msg 102, Level 15, State 1, Line 5Incorrect syntax near ')'.
here's the code:
USE drOfficeDBIF EXISTS (  SELECT *    FROM INFORMATION_SCHEMA.ROUTINES   WHERE SPECIFIC_SCHEMA = N'dbo'	 AND SPECIFIC_NAME = N'sp_todaysappt')   DROP PROCEDURE dbo.sp_todaysapptGOCREATE PROCEDURE dbo.sp_todaysappt@date date = GETDATE()ASSELECT  appointment.starttime,   patient.phone,   patient.patientId,   patient.fname+' '+patient.minitial+' '+patient.lname as fullname,   appointment.problem FROM		 appointment INNER JOIN					  patient ON appointment.patientId = patient.patientIdwhere apptdate = @dateGO-- =============================================-- Example to execute the stored procedure-- =============================================EXECUTE dbo.sp_todaysappt getdate()GO

This is the error from the second stored procedure:

Msg 102, Level 15, State 1, Procedure sp_patientHistory, Line 3Incorrect syntax near '.'.Msg 137, Level 15, State 2, Procedure sp_patientHistory, Line 11Must declare the scalar variable "@patientId".Msg 2812, Level 16, State 62, Line 5Could not find stored procedure 'dbo.sp_patientHistory'.
and here's the code:
IF EXISTS (  SELECT *    FROM INFORMATION_SCHEMA.ROUTINES   WHERE SPECIFIC_SCHEMA = N'dbo'	 AND SPECIFIC_NAME = N'sp_patientHistory')   DROP PROCEDURE dbo.sp_patientHistoryGOCREATE PROCEDURE dbo.sp_patientHistory@patientId int = patient.patientidASSELECT  patient.fname + ' ' + patient.lname as full_name,    appointment.apptdate, appointment.problem,    diagnosis.diganosis,    diagnosis.diagNotes,    perscription.perscription,    perscription.instructionswhere patientid = @patientId   FROM appointmentINNER JOIN diagnosis ON appointment.apptId = diagnosis.apptIdINNER JOIN patient ON appointment.patientId = patient.patientIdINNER JOIN perscription ON diagnosis.diagnosisId = perscription.diagnosisIdGO-- =============================================-- Example to execute the stored procedure-- =============================================EXECUTE dbo.sp_patientHistory 1GO

Link to comment
Share on other sites

You may need to add a GO statement after the USE statement. Other than that, the error messages don't really match up with the code, the line numbers that it's reporting problems on don't have the code that it says is a problem. I don't see a ')' on line 5, for example. You might also want to try a different variable name than "date", or another reserved word.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...