stored procedure

ALTER procedure [dbo].[spScribeTranscriptionClientFilter]  

(  

 @OfficeID INT = 1,  

 @PhysicianID VARCHAR(200) = '',  

 @StartDate varchar(20) = '',  

 @EndDate varchar(20) = '',  

 @DateType INT = -1,  

 @Status VARCHAR(200) ='',  

 @FirstName VARCHAR(200) = '',  

 @Lastname VARCHAR(200) = '',  

 @LocationCode VARCHAR(200) = '',  

 @VoiceJobNumber VARCHAR(200) = '',  

 @WorkType VARCHAR(200) ='' ,  

 @JobNumber VARCHAR(200)='',  

 @MRN VARCHAR(200)=''  

)  

as  

begin  

DECLARE @Office VARCHAR(100)   

DECLARE @PHYID  VARCHAR(500) = ''  

DECLARE @DATESTR VARCHAR(MAX) =''  

DECLARE @FName VARCHAR(MAX) =''  

DECLARE @LName VARCHAR(MAX) =''  

DECLARE @PStatus VARCHAR(MAX) =''  

DECLARE @LocCode VARCHAR(MAX) =''  

DECLARE @VoiceJobNo VARCHAR(MAX) =''  

DECLARE @WType VARCHAR(MAX) =''  

DECLARE @JobNo VARCHAR(MAX) =''  

DECLARE @MR# VARCHAR(MAX) =''  

SET @Office =  @OfficeID   

DECLARE @SQRYCondition  VARCHAR(MAX) =''  

  

 IF @DateType = 0  

 BEGIN  

 SET @DATESTR = ' AND DateCreated > ''' +  @StartDate  + ''' AND DateCreated < ''' + @EndDate +''''  

 END  

 IF @DateType = 1  

 BEGIN  

 SET @DATESTR = ' AND dbo.tblScribeTranscriptionAttributes.DtOfServ > ''' + @StartDate  + ''' AND dbo.tblScribeTranscriptionAttributes.DtOfServ < ''' + @EndDate +''''  

 END  

  IF @DateType = 2  

 BEGIN  

 SET @DATESTR = ' AND SubmitDate > ''' + @StartDate  + ''' AND SubmitDate < ''' + @EndDate +''''  

 END  

IF LEN(@PhysicianID)>10  

BEGIN  

 SET @PHYID = ' AND tblScribeTranscriptionClient.PhysicianID ='''+@PhysicianID +''''  

END  

IF LEN(@FirstName) > 0  

BEGIN  

 SET @FName= ' AND dbo.tblScribeTranscriptionAttributes.PFirstName LIKE '''+@FirstName + '%'''  

END  

IF LEN(@Lastname) > 0  

BEGIN  

 SET @LName= ' AND dbo.tblScribeTranscriptionAttributes.PLastName LIKE ''' +@Lastname + '%'''  

END  

IF LEN(@Status) > 1  

BEGIN  

 SET @PStatus= ' AND dbo.tblScribeTranscriptionClient.Status =' + @Status    

END  

IF LEN(@LocationCode) > 0  

BEGIN  

 SET @LocCode= ' AND dbo.tblScribeTranscriptionClient.LocCode=' + @LocationCode   

END  

IF LEN(@VoiceJobNumber) > 0  

BEGIN  

 SET @VoiceJobNo= ' AND dbo.tblScribeTranscriptionClient.VoiceJobNumber LIKE ''' + @VoiceJobNumber +'%'''  

END  

IF LEN(@WorkType) > 10  

BEGIN  

 SET @WType= ' AND dbo.tblScribeTranscriptionClient.TemplateID = '''+ @WorkType + ''''  

END  

IF LEN(@JobNumber) > 0  

BEGIN  

 SET @JobNo= ' AND dbo.tblScribeTranscriptionClient.JobNumber ='+ @JobNumber   

END  

IF LEN(@MRN) > 0  

BEGIN  

 SET @MR#= ' AND dbo.tblScribeTranscriptionAttributes.MedRN LIKE ''' + @MRN +'%'''  

END  

   

SET @SQRYCondition = @DATESTR + @PHYID + @FName +@LName + @PStatus + @LocCode + @VoiceJobNo + @WType + @JobNo + @MR#  

  

EXEC( 'SELECT 

tblScribeTranscriptionClient.TranscriptionID AS TranscriptionID,tblScribeTranscriptionClient.JobNumber,  

tblScribeTranscriptionClient.VoiceJobNumber as Voicejobnumber,tblScribeTranscriptionClient.TAT,  

tblScribeTranscriptionClient.DateCreated AS DOD, tblScribeTemplates.TemplateName AS WorkType ,tblScribeAccountLocation.LocName,  

tblScribeTranscriptionClient.AccountID,tblScribeTranscriptionClient.SubmitDate AS DateOfActivity,  

tblScribeTranscriptionClient.Status as statusId, dbo.tblScribeStatus.Descr AS Status,tblScribeTranscriptionAttributes.PFirstName ,  tblScribeTranscriptionAttributes.PFirstName AS FirstName ,

tblScribeTranscriptionAttributes.PLastName ,dbo.tblScribeTranscriptionAttributes.PLastName AS LastName ,tblScribeTranscriptionAttributes.DtOfServ AS DOS,  

tblScribeTranscriptionAttributes.PDOB AS DOB,tblScribeTranscriptionAttributes.APhyName AS APhyName,tblScribeTranscriptionClient.PhysicianID,  

tblScribephysicians.LastName  + '' '' + tblScribephysicians.FirstName as Physician,  

tblScribeTemplates.TemplateID AS templateId,tblScribeTemplates.TemplateName AS WorkType  ,

 dbo.tblScribeTranscriptionAttributes.MedRN AS MRN,

 AutoID ,Printed

FROM  dbo.tblScribeTranscriptionClient LEFT JOIN  dbo.tblScribeTranscriptionAttributes  

ON tblScribeTranscriptionClient.TranscriptionID = tblScribeTranscriptionAttributes.Transcriptionid  

LEFT JOIN tblScribephysicians ON tblScribeTranscriptionClient.PhysicianID = tblScribephysicians.PhysicianID  

LEFT JOIN tblScribeTemplates ON tblScribeTemplates.TemplateID = tblScribeTranscriptionClient.TemplateID  

LEFT JOIN dbo.tblScribeAccountLocation ON tblScribeTranscriptionClient.LocCode = tblScribeAccountLocation.LocCode   

left join tblScribeStatus ON tblScribeTranscriptionClient.Status= tblScribeStatus.Cstatus  

WHERE dbo.tblScribeTranscriptionClient.OfficeID = ' + @Office + @SQRYCondition)  

  

end