sql 多条件查询 拼接字符串 改成 普通查询格式

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

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。