Stored Procedure with multiple choice inputs

Transact-SQL
1

Hi,
I am tiring to find the best way to write this. I creating a procedure that has multiple inputs and in them multiple choices. these come in from an input form in an application
So, for example it has sale date, recall date, sale Id, current balance, company id.
this issue is that sale date or as starting sale date and ending sale date and this could also have this and the same choice recall date; and could have both, as well as the other items listed.
So, I could do this with quite a number of if statements like If recall date is null is Start recall date Null? this would get crazy.
I was wondering if anyone has a better way to write this ?

Thank you

2

I'd have an optional parameter for every possible value that needs to come in. Then have logic that did whatever checks were needed to determine how to do the processing based on what params were actually provided.

Yes, you could use a SELECT with CASE expressions rather than IF statements. I'd probably do that, as it would likely be easier to read and to maintain later.

3

Hi,

Thanks for the reply, sorry I am not sure how you suggesting of an optional parameter would work. can you explain that please.

Thank you

4

If you define a default value for a parameter, SQL treats it as optional. The standard approach is typically to use NULL when you want to leave a param unused.

For example:

CREATE PROCEDURE dbo.proc1
    @sale_date date = NULL,
    @recall_date date = NULL,
    @current_balance decimal(13, 2) = NULL,
    ...
AS 
SET NOCOUNT ON;
1 Like
5

I see what you mean thanks, but my issues is how do I write the case statement based on this.
So for example If the single date is null then the start and end date, of the dates, would be used (unless they were null) used but not sure how I would put that in the case statement.
Thank you

6

case statement in the the SELECT or in the WHERE clause

7

okay thanks

8

For example:

CREATE PROCEDURE ...
...params...
AS
SET NOCOUNT ON;
DECLARE @date_to_use_for_query date

SELECT @date_to_use_for_query = COALESCE(@sale_date, @recall_date, 
    @some_other_date, GETDATE())
1 Like
9

Thank you