如何在Sql中使用传递参数编写Sql查询

Question

我有4个参数在sql查询中动态传递，我想用where条件中参数过滤数据，至少有一个参数应该传值，其余的可能为空

参数为：@Orderstatusid、@paymentStatusid、@today、@fromdate、@todate

Answer 1

您需要将每个输入参数初始设置为NULL，然后通过传递必要的参数来覆盖它。

ALTER PROC YOUR_PROCEDURE_NAME
    (@Orderstatusid INT = NULL
     ,@paymentStatusid INT = NULL
     ,@today DATE = NULL
     ,@fromdate DATE = NULL
     ,@todate DATE = NULL
     )
AS
BEGIN
        -- YOUR CODE HERE....
        -- YOUR WHERE CLAUSE SHOULD HAVE
         Orderstatusid =    @Orderstatusid OR
         paymentStatusid = @paymentStatusid OR
         today = @today OR
         fromdate = @fromdate OR
         todate = @todate



END
GO

You can call your sp like below:

EXEC YOUR_PROCEDURE_NAME @Orderstatusid = '101'
OR
EXEC YOUR_PROCEDURE_NAME @today = 'YOUR_DATE'
...

Answer 2

您可以尝试此解决方案。

declare @whereCondition varchar(max)
declare @sql varchar(max) 

if (@Orderstatusid is not null)
set @whereCondition = 'Orderstatusid = ' + @Orderstatusid

if (@paymentStatusid is not null)
begin
if(@whereCondition !='')
begin
    set @whereCondition = @whereCondition +' and paymentStatusid = ' + @paymentStatusid --use and/or  as per your requirement
end
else
begin 
    set @whereCondition = ' paymentStatusid = ' + @paymentStatusid
end
end

-------write if condition for all cases

set @sql= 'select * from TABLENAME where ' + @whereCondition

exec @sql