Execute stored procedure with a nullable parameter from SQL server-CodePudding

I have a stored procedure, MYTASK1, with several parameters. It gets called something like this:

EXECUTE MYTASK1 '2021' , '1','1'

How do I run the stored procedure if the parameter is null. I tried

EXECUTE MYTASK1 '2021' , '' ,''

but it doesn't do anything.

my stored procedure

ALTER PROC MYTASK1 @Year varchar(6) = NULL,
                   @quarter varchar(6) = NULL,
                   @month varchar(6) = NULL
AS
BEGIN

    SELECT 'Sell Out' AS name,
           'Total Sales Amount' AS Description,
           'down' AS status,
           CAST(SUM(NetValue) AS decimal(9, 2)) AS amount
    FROM SalesAndReturns_RPT
    GROUP BY YEAR(Call_ActualStartDate),
             DATEPART(QUARTER, Call_ActualStartDate),
             MONTH(Call_ActualStartDate)
    HAVING YEAR(Call_ActualStartDate) = @Year
       AND DATEPART(QUARTER, Call_ActualStartDate) = @quarter
       AND MONTH(Call_ActualStartDate) = @month;

END;

CodePudding user response：

This is a guess, but I assume this is what you want:

ALTER PROC dbo.MYTASK1 @Year int = NULL, --Makes no sense as a varchar(6)
                       @Quarter int = NULL, --Makes no sense as a varchar(6)
                       @Month int = NULL --Makes no sense as a varchar(6)
AS
BEGIN

    SELECT 'Sell Out' AS name,
           'Total Sales Amount' AS Description,
           'down' AS status,
           CAST(SUM(NetValue) AS decimal(9, 2)) AS amount
    FROM dbo.SalesAndReturns_RPT
    WHERE ((Call_ActualStartDate >= DATEFROMPARTS(@Year, 1, 1) AND Call_ActualStartDate < DATEFROMPARTS(@Year 1, 1, 1)) OR @Year IS NULL)
      AND (DATEPART(QUARTER, Call_ActualStartDate) = @Quarter OR @Quarter IS NULL)
      AND (DATEPART(MONTH,Call_ActualStartDate) = @Month OR @Month IS NULL)
    OPTION (RECOMPILE);

END;

I have made the clause for @Year SARGable, however, with no information about on how @month and @quarter should be handled if @Year is NULL then this will likely make little difference, as those clauses are not SARGable. I have assumed that if @Year is NULL but @Month isn't, you would want the SUM for all rows in that month, regardless of the year it falls in.

If you do want a SARGable solution for this requirement, you'll need to implement a Calendar table.

CodePudding user response：

The code which you are executing: EXECUTE MYTASK1 '2021' , '' ,'' triggers procedure MYTASK1 with empty strings as parameters. Please note that empty string is it a different value than null... NULL is lacks of value, while empty string is totally valid string ;)

You can try EXECUTE MYTASK1 '2021' , NULL ,NULL

In your case NULL is a defoult value, so you can also use: EXECUTE MYTASK1 '2021'

CodePudding user response：

Since you have NULL as default, you can just not provide a parameter value at all

EXECUTE MYTASK1 '2021'

is the same as

EXECUTE MYTASK1 '2021', NULL, NULL

You can also use named parameters like so:

EXECUTE MYTASK1 @quarter='Q1'

However, going by other answers and comments, you have substantial issues with the contect of the stored proc.