如何通过使用SQL中的传递参数来编写SQL查询

Question

i有4个参数，这些参数正在SQL查询中动态传递，我想在条件和至少一个参数中以该参数为中的extric data，应该传递该值，而休息可能为null <</p>

参数为：@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