SQLServer的存储过程根据传参动态加载WHERE条件执行

场景：我这边需要根据传入的参数进行查询，如果参数没有值就不加上对应的查询条件。（就是类似 WHERE 后面的那些动态新增AND）

然后这些需要拼接后成字符串进行执行验证是否存在。

 

 

参考文心一言代码：

CREATE PROCEDURE usp_CheckDataExists
    @SearchKeyword NVARCHAR(100) = NULL, -- 模糊搜索关键词
    @Status INT = NULL                   -- 精确状态筛选
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @Params NVARCHAR(MAX);
    DECLARE @IsExists BIT;

    -- 1. 构建基础 SQL
    SET @SQL = N'SELECT @Result = CASE WHEN EXISTS (SELECT 1 FROM Orders WHERE 1=1';

    -- 2. 动态追加条件
    IF @SearchKeyword IS NOT NULL
    BEGIN
        SET @SQL = @SQL + N' AND OrderNo LIKE @KW';
    END

    IF @Status IS NOT NULL
    BEGIN
        SET @SQL = @SQL + N' AND Status = @St';
    END

    SET @SQL = @SQL + N') THEN 1 ELSE 0 END';

    -- 3. 定义参数映射
    SET @Params = N'@KW NVARCHAR(100), @St INT, @Result BIT OUTPUT';

    -- 4. 执行动态 SQL
    EXEC sp_executesql 
        @SQL, 
        @Params, 
        @KW = @SearchKeyword, 
        @St = @Status, 
        @Result = @IsExists OUTPUT;

    -- 5. 返回结果或后续逻辑
    IF @IsExists = 1
        PRINT '数据存在';
    ELSE
        PRINT '数据不存在';
        
    SELECT @IsExists AS IsDataExists;
END
GO

实际：

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

--20261012
--DZW
--WRAPDataOperaIssue
ALTER procedure [dbo].[proc_WRAPDataOpera_Issue]
	@CreateID INT,
	@Depot VARCHAR(100) = '',
	@RMANo VARCHAR(100) = '',
	@StartTime DATETIME = NULL,
	@EndTime DATETIME = NULL,
	@OutResult VARCHAR(200) OUT
AS 
SET NOCOUNT ON

BEGIN
	DECLARE @err INT = 0


	--Start验证
	--
	DECLARE @SQL NVARCHAR(MAX);
    DECLARE @IsExists1 BIT,@IsExists2 BIT,@IsExists3 BIT,@IsExists4 BIT;
    DECLARE @Params NVARCHAR(MAX);
	
	DECLARE @vaildateSqlFirstStr VARCHAR(MAX),@vaildateSqlLastStr VARCHAR(MAX)
	SET @vaildateSqlFirstStr = 'SELECT @Result = CASE WHEN EXISTS (';
	SET @vaildateSqlFirstStr = @vaildateSqlFirstStr + 'SELECT 1 FROM dbo.OrderNo_WRAPData A WHERE A.CurStatus = 0 '

	SET @vaildateSqlLastStr = ') THEN 1 ELSE 0 END';


    -- 动态追加条件
    IF (ISNULL(@Depot,'') != '')
    BEGIN
        SET @vaildateSqlFirstStr = @vaildateSqlFirstStr + N' AND A.Depot = @Depot';
    END
    IF (ISNULL(@RMANo,'') != '')
    BEGIN
        SET @vaildateSqlFirstStr = @vaildateSqlFirstStr + N' AND A.RMANo = @RMANo';
    END
    IF (ISNULL(@StartTime,'') != '')
    BEGIN
        SET @vaildateSqlFirstStr = @vaildateSqlFirstStr + N' AND A.CreateTime >= @StartTime';
    END
    IF (ISNULL(@EndTime,'') != '')
    BEGIN
        SET @vaildateSqlFirstStr = @vaildateSqlFirstStr + N' AND A.CreateTime < @EndTime';
    END


	--1.PN基础表有数据
	
	SET @SQL = @vaildateSqlFirstStr + ' AND NOT EXISTS(SELECT 1 FROM dbo.BaseData_Part P ' +
	' WHERE P.StockingID = A.StkID)'

    SET @SQL = @SQL + @vaildateSqlLastStr;
	
    SET @Params = N'@Depot VARCHAR(100), @RMANo VARCHAR(100),@StartTime VARCHAR(100),@EndTime VARCHAR(100), @Result BIT OUTPUT';

	EXEC sys.sp_executesql 
        @SQL, 
        @Params, 
        @Depot = @Depot,
		@RMANo = @RMANo,
		@StartTime = @StartTime,
		@EndTime = @EndTime,
        @Result = @IsExists1 OUTPUT;
	IF @IsExists1 = 1
	BEGIN
		SET @OutResult = 'ISSUE的数据PN必须存在PN基础表！';
		RETURN;
	END

	--2.PPID/SN没有重复

	SET @SQL = @vaildateSqlFirstStr + ' GROUP BY A.SN HAVING COUNT(A.SN) > 1'

    SET @SQL = @SQL + @vaildateSqlLastStr;
	
    SET @Params = N'@Depot VARCHAR(100), @RMANo VARCHAR(100),@StartTime VARCHAR(100),@EndTime VARCHAR(100), @Result BIT OUTPUT';

	EXEC sys.sp_executesql 
        @SQL, 
        @Params, 
        @Depot = @Depot,
		@RMANo = @RMANo,
		@StartTime = @StartTime,
		@EndTime = @EndTime,
        @Result = @IsExists2 OUTPUT;

	IF @IsExists2 = 1
	BEGIN
		SET @OutResult = 'ISSUE的数据SN不能重复！';
		RETURN;
	END

	--3.导入有价格
	SET @SQL = @vaildateSqlFirstStr + ' AND ISNULL(A.Price,0) = 0'

    SET @SQL = @SQL +@vaildateSqlLastStr;
	
    SET @Params = N'@Depot VARCHAR(100), @RMANo VARCHAR(100),@StartTime VARCHAR(100),@EndTime VARCHAR(100), @Result BIT OUTPUT';

	EXEC sys.sp_executesql 
        @SQL, 
        @Params, 
        @Depot = @Depot,
		@RMANo = @RMANo,
		@StartTime = @StartTime,
		@EndTime = @EndTime,
        @Result = @IsExists3 OUTPUT;

	IF @IsExists3 = 1
	BEGIN
		SET @OutResult = 'ISSUE的数据必须有价格！';
		RETURN;
	END

	--4.导入有HS
	SET @SQL = @vaildateSqlFirstStr + ' ISNULL(A.HS_Code,'''') = '''' '

    SET @SQL = @SQL + @vaildateSqlLastStr;
	
    SET @Params = N'@Depot VARCHAR(100), @RMANo VARCHAR(100),@StartTime VARCHAR(100),@EndTime VARCHAR(100), @Result BIT OUTPUT';

	EXEC sys.sp_executesql 
        @SQL, 
        @Params, 
        @Depot = @Depot,
		@RMANo = @RMANo,
		@StartTime = @StartTime,
		@EndTime = @EndTime,
        @Result = @IsExists4 OUTPUT;
	IF @IsExists4 = 1
	BEGIN
		SET @OutResult = 'ISSUE的数据必须有HS！';
		RETURN;
	END


	--END验证

	BEGIN TRANSACTION

		Declare @s varchar(8000),@WhereClause VARCHAR(MAX)

		IF (ISNULL(@Depot,'') != '')
		BEGIN
			SET @WhereClause = @WhereClause + N' AND A.Depot = @Depot';
		END
		IF (ISNULL(@RMANo,'') != '')
		BEGIN
			SET @WhereClause = @WhereClause + N' AND A.RMANo = @RMANo';
		END
		IF (ISNULL(@StartTime,'') != '')
		BEGIN
			SET @WhereClause = @WhereClause + N' AND A.CreateTime >= @StartTime';
		END
		IF (ISNULL(@EndTime,'') != '')
		BEGIN
			SET @WhereClause = @WhereClause + N' AND A.CreateTime < @EndTime';
		END

		set @s = 'UPDATE dbo.OrderNo_WRAPData SET IssueTime = GETDATE(),CurStatus = 1,IssueID = ' + @CreateID 
		+ ' WHERE 1 = 1 '+ @WhereClause;
		exec (@s)
		set @err = @err +@@error 
		  
	--执行结果
	if @err=0 
	begin
		Commit Transaction
		set @OutResult='success' 
	end
	else
	begin
		Rollback Transaction
		set @OutResult='Issue Fail！' 
	end

end
GO

　

 

参阅：文心一言