sql 多条件查询拼接字符串改成普通查询格式-优快云博客

本文介绍了一个用于检查服务需求订单的SQL Server存储过程。该过程通过输入参数筛选tbl_ServiceDemandOrder表中的记录，并返回匹配的数据。输入参数包括订单编号、公司名称、部门名称等。

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