Stored Procedure

Question posted by: sweatha (Member) on July 3rd, 2008 05:55 AM

Hi

I have to convert the following query into stored procedure

"select DoctorMaster.FirstName+''+DoctorMaster.MiddleName+ ''+DoctorMaster.LastName as DoctorsName,DoctorMaster.DRID,DoctorMaster.Gender, DrClinicDetails.Address,DoctorMaster.Telephone+',' +DoctorMaster.Mobile as Phone,PrimaryTitle.PrimaryTitle from DoctorMaster,DrClinicDetails,PrimaryTitle,Specialt y where DoctorMaster.FirstName like '" + obj.ToString() + "%' or Specialty.Specialty='" + obj1.ToString() + "' or DoctorMaster.City='" + obj2.ToString() + "' and DoctorMaster.DRID=DrClinicDetails.DRID and DoctorMaster.SPID=Specialty.SPID and PrimaryTitle.PTID=DoctorMaster.PrimaryTitleID"

For that I have written the stored procedure as

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Pro_Grid]

@FirstName nvarchar(100)output,
@MiddleName nvarchar(100)output,
@LastName nvarchar(100)output,
@DRID nvarchar(12)output,
@Gender bit output,
@Address nvarchar(255)output,
@Telephone nvarchar(50)output,
@Mobile nvarchar(50)output,
@PrimaryTitle nvarchar(20)output,
@Specialty nvarchar(50)output,
@City nvarchar(100)output
@SPID nvarchar(12)output,
@PTID nvarchar(12)output,
@PrimaryTitleID nvarchar(12)output


AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.


-- Insert statements for procedure here
-- select @SPID=SPID,@Specialty=Specialty from Specialty

select DoctorMaster.FirstName+''+DoctorMaster.MiddleName+ ''+DoctorMaster.LastName as DoctorsName,DoctorMaster.DRID,DoctorMaster.Gender, DrClinicDetails.Address,DoctorMaster.Telephone+',' +DoctorMaster.Mobile as Phone,PrimaryTitle.PrimaryTitle from DoctorMaster,DrClinicDetails,PrimaryTitle,Specialt y where DoctorMaster.FirstName like DoctorMaster.@FirstName or Specialty.Specialty=Specialty.@Specialty or DoctorMaster.City=DoctorMaster.@City and DoctorMaster.DRID=DrClinicDetails.DRID and DoctorMaster.SPID=Specialty.SPID and PrimaryTitle.PTID=DoctorMaster.PrimaryTitleID
-- insert into Specialty (SPID,Specialty)
-- values(@SPID,@Specialty)

END

But if I execute, I got the error as

Msg 102, Level 15, State 1, Procedure Pro_Grid, Line 20
Incorrect syntax near '@SPID'.
Msg 102, Level 15, State 1, Procedure Pro_Grid, Line 34
Incorrect syntax near '@FirstName'.

Not the answer you were looking for? Post your question . . .
190,473 Experts ready to help you find a solution.
Sign up for a free account, or Login (if you're already a member).