CODEWITHSUNDEEP
.netsqlparameters
Laziale
Laziale

Reputation: 8225

sql parameter causing problems

I have a stored procedure with which I am populating gridview in .net program.

The problem is when I am populating that parameter, and sending for execution to the database, I don't get nothing in return.

When I am executing the query without that parameter, then the application is returning data without any problems.

Any idea what can be the problem?

Some code:

if (ddlCountries.SelectedIndex > 0)
{
   commAdvanced.Parameters.Add("@ShippingCountry", SqlDbType.NVarChar).Value = shippingCountry;
}
else
{
   commAdvanced.Parameters.Add("@ShippingCountry", SqlDbType.NVarChar).Value = DBNull.Value;
}

and here is the stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE Dbo_SearchAll
(
    @StartTime datetime = null,
    @EndTime datetime = null,
    @CustomerEmail nvarchar(255) = null,
    @OrderStatusID nvarchar = null,
    @PaymentStatusID nvarchar = null,
    @Username nvarchar(255) = null,
    @CustomerName nvarchar(255) = null, 
    @OrderNumber int = null,
    @MinimumOrderAmount decimal = null, 
    @MaximumOrderAmount decimal = null,
    @ShippingMethod nvarchar = null,
    @SKU nvarchar(255) = null,
    @CouponID int = null,
    @DiscountType int = null,
    @ShippingCountry nvarchar = null
)
AS BEGIN
    SET NOCOUNT ON

    SELECT DISTINCT 
         o.OrderID, o.OrderTotal, o.ShippingCountry, 
         n.Name AS OrderStatus, 
         p.Name AS PaymentStatus 
    FROM 
         Order o
    JOIN 
         OrderStatus n ON o.OrderStatusID = n.OrderStatusID
    JOIN 
         PaymentStatus p ON o.OrderStatusID = p.PaymentStatusID 
    JOIN 
         Customer c ON o.CustomerID = c.CustomerID
    JOIN 
         OrderVariationsPeople op ON o.OrderID = op.OrderID
    JOIN 
         VariousPeople pv ON op.ProductID = pv.ProductId
    WHERE
         (o.CreatedOn > @StartTime OR @StartTime IS NULL)
         AND (o.CreatedOn < @EndTime OR @EndTime IS NULL)
         AND (o.ShippingEmail = @CustomerEmail OR @CustomerEmail IS NULL)
         AND (o.OrderStatusID IN (@OrderStatusID) OR @OrderStatusID IS NULL)
         AND (o.PaymentStatusID IN (@PaymentStatusID) OR @PaymentStatusID IS NULL)
         AND (c.Username = @Username OR @Username IS NULL)
         AND (o.BillingFirstName + ' ' + o.BillingLastName = @CustomerName OR @CustomerName IS NULL)
         AND (o.ShippingFirstName + ' ' + o.ShippingLastName = @CustomerName OR @CustomerName IS NULL)
         AND (o.OrderID = @OrderNumber OR @OrderNumber IS NULL)
         AND (o.OrderTotal > @MinimumOrderAmount or @MinimumOrderAmount IS NULL)
         AND (o.OrderTotal < @MaximumOrderAmount OR @MaximumOrderAmount IS NULL)
         AND (o.ShippingMethod = @ShippingMethod OR @ShippingMethod IS NULL)
         AND (pv.SKU = @SKU OR @SKU IS NULL)
         AND (o.DiscountID = @DiscountType OR @DiscountType IS NULL)
         AND (o.ShippingCountry = @ShippingCountry OR @ShippingCountry IS NULL)
    ORDER BY 
         o.OrderID
END

Any idea why that parameter is causing errors?

Thanks in advance

Upvotes: 1

Views: 135

Answers (2)

Joshua Smith
Joshua Smith

Reputation: 1004

Have you tried executing the proc outside of your application with some known good param values? Does it ever return data when @ShippingCountry is not null?

Upvotes: 0

Dustin Laine
Dustin Laine

Reputation: 38503

Try this, I would do it for all your clauses.

@ShippingCountry IS NOT NULL AND o.ShippingCountry = @ShippingCountry

You can also not pass a parameter when NULL as you default your parameter to NULL in SP.

Upvotes: 2

Related Questions