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'.