CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
user3006876
user3006876

Reputation: 47

How to make 1 parameter optional in a SQL stored procedure

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:

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?

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

Upvotes: 2

Views: 263

Answers (3)

xacinay
xacinay

Reputation: 909

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.

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

Upvotes: 0

Jaaz Cole
Jaaz Cole

Reputation: 3180

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)

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

Upvotes: 2

Ryan Schlueter
Ryan Schlueter

Reputation: 2221

 @Indicator varchar(255)  = ""

Will give it a default value

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

will only check if its not empty

Upvotes: 2

Related Questions