set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROC [dbo].[usp_SRV_CheckServiceDemandOrder] @AInsNO NVARCHAR(50) =null,--必填 @ACompanyName NVARCHAR(50) = null,--必填 @ADepartmentName NVARCHAR(50) = null, @AName NVARCHAR(50) = null, --必填 @ApplicantID NVARCHAR(50)= null, @APhone VARCHAR(50)= null,--电话 @ACellPhone VARCHAR(50)= null,--手机 @AEmail VARCHAR(50)= null, @AFax NVARCHAR(50)= null, @AcceptWay TINYINT= null, @OneLevelSortID INT= null, @TwoLevelSortID INT= null, @ThreeLevelSortID INT= null, @LogContent NVARCHAR(MAX)= null AS /* PAGE: Action: CreatedBy: CreatedDate: ModifiedHistory: Test Scripts: DECLARE @return_value int EXEC @return_value = [dbo].[usp_SRV_CheckServiceDemandOrder] @AInsNO = N‘00010000‘, @LogContent = N‘日‘ SELECT ‘Return Value‘ = @return_value GO */ SET NOCOUNT ON SELECT * FROM tbl_ServiceDemandOrder WHERE (@AInsNO IS NULL OR AInsNO = @AInsNO ) AND (@ACompanyName IS NULL OR ACompanyName = @ACompanyName ) AND (@ADepartmentName IS NULL OR ADepartmentName = @ADepartmentName ) AND (@AName IS NULL OR AName = @AName ) AND (@ApplicantID IS NULL OR ApplicantID = @ApplicantID ) AND (@APhone IS NULL OR APhone = @APhone ) AND (@ACellPhone IS NULL OR ACellPhone = @ACellPhone ) AND (@AEmail IS NULL OR AEmail = @AEmail ) AND (@AFax IS NULL OR AFax = @AFax ) AND (@AcceptWay IS NULL OR AcceptWay = @AcceptWay ) AND (@OneLevelSortID IS NULL OR OneLevelSortID = @OneLevelSortID ) AND (@TwoLevelSortID IS NULL OR TwoLevelSortID = @TwoLevelSortID ) AND (@ThreeLevelSortID IS NULL OR ThreeLevelSortID = @ThreeLevelSortID ) AND ((@LogContent IS NULL) OR (ISNULL(LogContent,‘‘) like ‘%‘+ISNULL(@LogContent,‘‘)+‘%‘)) -- --DECLARE @tmpTable TABLE() -- DECLARE @sqlStr VARCHAR(MAX) -- SET @sqlStr = ‘ -- SELECT * FROM dbo.tbl_ServiceDemandOrder -- WHERE AInsNO = ‘+ @AInsNO -- + ‘ AND ACompanyName LIKE AND AName = ‘‘‘+ @AName+‘‘‘‘ -- -- IF(NULLIF(@ADepartmentName,‘‘) IS NOT null) -- BEGIN -- SET @sqlStr = @sqlStr + ‘ AND ADepartmentName = ‘‘‘+ @ADepartmentName +‘‘‘‘ -- END -- IF(NULLIF(@ApplicantID,‘‘) IS NOT null) -- BEGIN -- SET @sqlStr = @sqlStr + ‘ AND ApplicantID = ‘‘‘+ @ApplicantID +‘‘‘‘ -- END -- IF(NULLIF(@APhone,‘‘) IS NOT null) -- BEGIN -- SET @sqlStr = @sqlStr + ‘ AND APhone = ‘‘‘+ @APhone +‘‘‘‘ -- END -- IF(NULLIF(@ACellPhone,‘‘) IS NOT null) -- BEGIN -- SET @sqlStr = @sqlStr + ‘ AND ACellPhone = ‘‘‘+ @ACellPhone +‘‘‘‘ -- END -- IF(NULLIF(@AEmail,‘‘) IS NOT null) -- BEGIN -- SET @sqlStr = @sqlStr + ‘ AND AEmail = ‘‘‘+ @AEmail +‘‘‘‘ -- END -- IF(NULLIF(@AFax,‘‘) IS NOT null) -- BEGIN -- SET @sqlStr = @sqlStr + ‘ AND AFax = ‘‘‘+ @AFax +‘‘‘‘ -- END -- IF(NULLIF(@ReqDescription,‘‘) IS NOT null) -- BEGIN -- SET @sqlStr = @sqlStr + ‘ AND ReqDescription = ‘‘‘+ @ReqDescription +‘‘‘‘ -- END -- EXEC(@sqlStr) SET NOCOUNT OFF