demuro1 Posted December 12, 2012 Share Posted December 12, 2012 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 More sharing options...
justsomeguy Posted December 12, 2012 Share Posted December 12, 2012 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now