How to Fix SQL Error Invalid Parameter in Stored Procedures

I’ll walk you through the process of creating and enhancing a SQL Server stored procedure to analyze shipping data. We’ll start with a basic implementation, identify its limitations, and iteratively improve it to make it more robust, flexible, and user-friendly. By the end, you’ll have a practical, production-ready stored procedure that demonstrates best practices in SQL development.

Analyzing Shipping Data

Imagine you’re working with an e-commerce database that tracks customer orders. One common task is to retrieve all orders shipped on a specific date. While this seems simple, there are several challenges to address:

1. Input Validation: What if the user forgets to provide a shipping date?
2. Error Handling: How do we inform the user of errors in a clear and actionable way?
3. Flexibility: Can we allow users to sort results in ascending or descending order?
4. User Experience: How do we handle cases where no orders match the criteria?

Let’s tackle these challenges step by step.

The Basic Stored Procedure

We’ll start with a simple stored procedure that retrieves orders shipped on a specific date. Here’s the initial implementation:

CREATE PROCEDURE shipping_date_sp 
    @shippedDate DATE
AS
BEGIN
    -- Check if the input parameter is NULL
    IF @shippedDate IS NULL
    BEGIN
        PRINT 'Please enter a valid ship date';
        RETURN; -- Exit the procedure if no valid input is provided
    END

    -- Proceed with the query if the input parameter is valid
    SELECT 
        o.OrderID, 
        c.CompanyName, 
        c.Phone, 
        CONVERT(VARCHAR(20), o.OrderDate, 107) AS OrderDate,
        CONVERT(VARCHAR(20), o.RequiredDate, 107) AS RequiredDate, 
        CONVERT(VARCHAR(20), o.ShippedDate, 107) AS ShippedDate
    FROM 
        Orders AS o
    JOIN 
        Customers AS c ON c.CustomerID = o.CustomerID
    WHERE 
        o.ShippedDate = @shippedDate
    ORDER BY 
        o.OrderDate;
END

Explanation

1. Parameter Check: The procedure checks if @shippedDate is NULL. If it is, it prints an error message and exits using RETURN.
2. Query Execution: If the input is valid, it retrieves orders shipped on the specified date, joining the Orders and Customers tables.
3. Date Formatting: The CONVERT function formats dates into a readable format (e.g., Mar 12, 2024).

Limitations

• No feedback if no orders match the criteria.
• No flexibility in sorting results.
• Limited error handling (uses PRINT, which is less visible).

Enhancing the Stored Procedure

Let’s address the limitations by adding the following features:

1. Default Parameter Value: Allow the procedure to be called without a parameter.
2. Improved Error Handling: Use RAISERROR for better visibility.
3. Sort Order Flexibility: Allow users to specify ascending or descending order.
4. Additional Data Points: Include the customer’s country and calculate the number of days it took to ship the order.
5. No Results Feedback: Inform the user if no orders match the criteria.

Here’s the enhanced version:

CREATE PROCEDURE shipping_date_sp 
    @shippedDate DATE = NULL,
    @sortOrder NVARCHAR(4) = 'ASC'
AS
BEGIN
    -- Validate required parameter
    IF @shippedDate IS NULL
    BEGIN
        RAISERROR('Error: @shippedDate parameter is required. Please provide a shipping date.', 16, 1);
        RETURN;
    END

    -- Validate sort order
    IF UPPER(@sortOrder) NOT IN ('ASC', 'DESC')
    BEGIN
        RAISERROR('Invalid sort order. Use ''ASC'' (ascending) or ''DESC'' (descending).', 16, 1);
        RETURN;
    END

    -- Dynamic SQL for safe query construction
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'
        SELECT 
            o.OrderID, 
            c.CompanyName, 
            c.Phone,
            c.Country,
            CONVERT(VARCHAR(20), o.OrderDate, 107) AS OrderDate,
            CONVERT(VARCHAR(20), o.RequiredDate, 107) AS RequiredDate, 
            CONVERT(VARCHAR(20), o.ShippedDate, 107) AS ShippedDate,
            DATEDIFF(DAY, o.OrderDate, o.ShippedDate) AS DaysToShip
        FROM 
            Orders AS o
        JOIN 
            Customers AS c ON c.CustomerID = o.CustomerID
        WHERE 
            o.ShippedDate = @inputDate
        ORDER BY 
            o.OrderDate ' + @sortOrder + ';';

    -- Execute dynamic SQL with parameter
    EXEC sp_executesql @sql, 
        N'@inputDate DATE', 
        @inputDate = @shippedDate;

    -- Provide feedback if no results
    IF @@ROWCOUNT = 0
    BEGIN
        PRINT 'No orders found shipped on: ' + CONVERT(VARCHAR, @shippedDate, 107);
    END
END

Key Enhancements

1. Default Parameter Value: @shippedDate defaults to NULL, allowing the procedure to be called without parameters.
2. Improved Error Handling: RAISERROR provides more visible and actionable error messages.
3. Sort Order Flexibility: Users can specify ASC or DESC for sorting.
4. Additional Data Points: The query now includes Country and calculates DaysToShip.
5. No Results Feedback: A message is printed if no orders match the criteria.

Testing the Procedure

Let’s test the procedure with different scenarios:

Valid Input

EXEC shipping_date_sp @shippedDate = '2023-10-01', @sortOrder = 'DESC';

• Retrieves orders shipped on October 1, 2023, sorted in descending order.

Missing Parameter

EXEC shipping_date_sp;

• Output: Error: @shippedDate parameter is required. Please provide a shipping date.

Invalid Sort Order

EXEC shipping_date_sp @shippedDate = '2023-10-01', @sortOrder = 'RANDOM';

• Output: Invalid sort order. Use 'ASC' (ascending) or 'DESC' (descending).

No Results

EXEC shipping_date_sp @shippedDate = '2099-01-01';

• Output: No orders found shipped on: Jan 01, 2099

Final Thoughts

This enhanced stored procedure demonstrates how to build robust, user-friendly database solutions. By incorporating input validation, dynamic SQL, and clear error messaging, we’ve created a tool that is both flexible and reliable.