stored procedure

Question

Hi All I have stored procedure for search i have three parameter all parameter are optional i am getting all record if i am enter MRN=1or1=1 so how can i restrict this kind of value by stored procedure . if this kind of value enter by user no record should display

Ravi Patel · Answer

Hi Sanjay  here is sp 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 END IF @DateType = 1 BEGIN SET @DATESTR = ' AND dbo.tblScribeTranscriptionAttributes.DtOfServ > ''' + @StartDate + ''' AND dbo.tblScribeTranscriptionAttributes.DtOfServ END IF @DateType = 2 BEGIN SET @DATESTR = ' AND SubmitDate > ''' + @StartDate + ''' AND SubmitDate 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 TranscriptionIDtblScribeTranscriptionClient.JobNumber tblScribeTranscriptionClient.VoiceJobNumber as VoicejobnumbertblScribeTranscriptionClient.TAT tblScribeTranscriptionClient.DateCreated AS DOD tblScribeTemplates.TemplateName AS WorkType tblScribeAccountLocation.LocName tblScribeTranscriptionClient.AccountIDtblScribeTranscriptionClient.SubmitDate AS DateOfActivity tblScribeTranscriptionClient.Status as statusId dbo.tblScribeStatus.Descr AS StatustblScribeTranscriptionAttributes.PFirstName  tblScribeTranscriptionAttributes.PFirstName AS FirstName  tblScribeTranscriptionAttributes.PLastName dbo.tblScribeTranscriptionAttributes.PLastName AS LastName tblScribeTranscriptionAttributes.DtOfServ AS DOS tblScribeTranscriptionAttributes.PDOB AS DOBtblScribeTranscriptionAttributes.APhyName AS APhyNametblScribeTranscriptionClient.PhysicianID tblScribephysicians.LastName + '' '' + tblScribephysicians.FirstName as Physician tblScribeTemplates.TemplateID AS templateIdtblScribeTemplates.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

Khan Abrar Ahmed · Answer

Hi in store procedure check the value if it is one then add Return sysntax.

Sanjay Kumar · Answer

Hi ravi stored procedure share structure