如何在SQL存储过程中选择一个参数?

I have the following stored procedure that will ultimately get built into a .NET application. Pretty basic... the user will select the parameters and receive two lists of data based on the selection:

我有以下存储过程，最终将构建到. net应用程序中。基本…用户将选择参数，并根据选择接收两份数据列表:

CREATE PROCEDURE [dbo].[sp_GetList] 
    @Chain varchar(255),
    @EffectiveDate datetime,
    @ClientName varchar(255),
    @Indicator varchar(255) 
AS 

SELECT *
FROM dbo.Archive
WHERE XX_Effective_Date = @EffectiveDate
  AND XX_Chain = @Chain
  AND XX_Client_Name = @ClientName
  AND XX_Indicator = @Indicator

SELECT *
FROM dbo.Archive2
WHERE XX_Chain = @Chain
  AND XX_Effective_Date = @EffectiveDate

GO

This would work fine if the first SELECT statement always needed all four parameters, however that is not the case. In some cases to get the correct result, the user must select all the parameters except for @Indicator. When I go to execute this stored procedure as it is, it fails because it expects a parameter for @Indicator. How can make this ignore the "AND XX_Indicator = @Indicator" line when the @Indicator parameter is left blank?

如果第一个SELECT语句总是需要所有四个参数，那么这将很好地工作，但是情况并非如此。在某些情况下，为了得到正确的结果，用户必须选择除@指示器以外的所有参数。当我执行这个存储过程时，它失败了，因为它需要一个@Indicator参数。当@Indicator参数为空时，如何使它忽略“和XX_Indicator = @Indicator”行呢?

Thanks and let me know if you need any additional info.

谢谢，如果你需要更多的信息，请告诉我。

3 个解决方案

#1

 @Indicator varchar(255)  = ""

Will give it a default value

会给它一个默认值吗

AND (XX_Indicator = @Indicator or @Indicator = '')

和(XX_Indicator = @Indicator或@Indicator = ")

will only check if its not empty

将只检查它是否为空

#2

Declare a default value for the parameter I.E. @Chain varchar(255) = NULL so that in your query, you can change the predicate to be XX_Chain = ISNULL(@Chain,XX_Chain)

为参数声明一个默认值，即@Chain varchar(255) = NULL，以便在查询中可以将谓词更改为XX_Chain = ISNULL(@Chain,XX_Chain)

Both combined will make the given parameter not required, and also not restrict the query results.

两者结合将使给定的参数不需要，也不限制查询结果。

#3

Declare omittable @Indicator variable as = NULLand in select stetement use CASE. The following operation will be transformed into Constant=Constant for omitted param Indicator and will cost approx. nothing in addition to usual query.

在select stetement用例中，将omittable @Indicator变量声明为= NULLand。以下操作将转换为常量=常量，用于忽略参数指示器，成本约为。除了通常的查询之外什么都没有。

CREATE PROCEDURE [dbo].[sp_GetList] 
    @Chain varchar(255),
    @EffectiveDate datetime,
    @ClientName varchar(255),
    @Indicator varchar(255) = NULL 
AS 

SELECT *
FROM dbo.Archive
WHERE XX_Effective_Date = @EffectiveDate
  AND XX_Chain = @Chain
  AND XX_Client_Name = @ClientName
  AND @Indicator = 
    case when @Indicator is null then @Indicator else XX_Indicator end;

SELECT *
FROM dbo.Archive2
WHERE XX_Chain = @Chain
  AND XX_Effective_Date = @EffectiveDate

#1

 @Indicator varchar(255)  = ""