CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresconditional-statementsleft-join
chris c
chris c

Reputation: 331

Parameterized stored procedure conditional join with search in joined table by 3 parameters

I would like my stored procedure to have a conditional join, where if one of three/or all or two conditions are met then we join two tables (address to order) and return the results from the order table based on search conditions in the address table.

So here is my stored procedure:

USE [Test]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[OrderLoadAllPaged]
    @OrderId int = 0,
    @WarehouseId int = 0,
    @PaymentMethodSystemName nvarchar(max) = null,
    @OrderStatusId int = 0,
    @PaymentStatusId int = 0,
    @ShippingStatusId int = 0,
    @BillingEmail nvarchar(max) = null,
    @BillingFirstName nvarchar(max) = null,
    @BillingLastName nvarchar(max) = null,
    @ShippingMethod nvarchar(max) = null,
    @CreatedFromUtc datetime = null,
    @CreatedToUtc datetime = null
AS
BEGIN
    DECLARE
        @sql nvarchar(max)

    SET NOCOUNT ON;

        SELECT TOP 100 *
        FROM [Test].[dbo].[Order] o with (NOLOCK)

    LEFT join [Test].[dbo].[Address] a on (a.Id LIKE o.BillingAddressId)
    WHERE (@BillingEmail IS null OR a.[Email] LIKE '%@BillingEmail%') 
    AND (@BillingFirstName IS null OR a.[FirstName] LIKE '%@BillingFirstName%')
    AND (@BillingLastName IS null OR a.[LastName] LIKE '%@BillingLastName%')

    AND
        o.[Deleted] = 0

    AND (o.[Id] = @OrderId OR @OrderId = 0)
    AND (o.[WarehouseId] = @WarehouseId OR @WarehouseId = 0)

    AND (@PaymentMethodSystemName IS null OR o.[PaymentMethodSystemName] = @PaymentMethodSystemName)
    AND (o.[OrderStatusId] = @OrderStatusId OR @OrderStatusId = 0)
    AND (o.[PaymentStatusId] = @PaymentStatusId OR @PaymentStatusId = 0)

    AND (o.[ShippingStatusId] = @ShippingStatusId OR @ShippingStatusId = 0)

    AND (@ShippingMethod IS null OR o.[ShippingMethod] = @ShippingMethod)
    AND o.[CreatedOnUtc] >= ISNULL(@CreatedFromUtc, '1/1/1900')
    AND o.[CreatedOnUtc] < ISNULL(@CreatedToUtc, '1/1/2999')

    ORDER BY o.[CreatedOnUtc] DESC
END

This is the bit I'm having trouble working out:

LEFT join [Test].[dbo].[Address] a on (a.Id LIKE o.BillingAddressId)
    WHERE (@BillingEmail IS null OR a.[Email] LIKE '%@BillingEmail%') 
    AND (@BillingFirstName IS null OR a.[FirstName] LIKE '%@BillingFirstName%')
    AND (@BillingLastName IS null OR a.[LastName] LIKE '%@BillingLastName%')

I felt like I was on the right track, but I'm getting some kind of timeout error

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

which tells me something must be wrong with the query.

If I remove my email, firstname and lastname parameters the query completes.

I can confirm this is how I would like it to work, as I manually wrote up the query and it's what I need:

SELECT TOP 100 *
  FROM [Test].[dbo].[Order] o
  LEFT JOIN  [Test].[dbo].[Address] a
  ON o.[BillingAddressId] = a.Id
  WHERE a.[Email] LIKE '%chris1%' AND a.FirstName LIKE '%chris%' AND a.LastName LIKE '%c%'
  ORDER BY o.[CreatedOnUtc]

My parameters are sent to database in C# code like so:

var pBillingEmail = _dataProvider.GetParameter();
pBillingEmail.ParameterName = "BillingEmail";
pBillingEmail.Value = (!String.IsNullOrEmpty(billingEmail)) ? (object)billingEmail : DBNull.Value;
pBillingEmail.DbType = DbType.String;

var pBillingFirstName = _dataProvider.GetParameter();
pBillingFirstName.ParameterName = "BillingFirstName";
pBillingFirstName.Value = (!String.IsNullOrEmpty(billingFirstName)) ? (object)billingFirstName : DBNull.Value;
pBillingFirstName.DbType = DbType.String;

var pBillingLastName = _dataProvider.GetParameter();
pBillingLastName.ParameterName = "BillingLastName";
pBillingLastName.Value = (!String.IsNullOrEmpty(billingLastName)) ? (object)billingLastName : DBNull.Value;
pBillingLastName.DbType = DbType.String;

Anyone know where I'm going wrong with this?

Upvotes: 2

Views: 263

Answers (2)

Dale K
Dale K

Reputation: 27290

LIKE '%@BillingEmail%' should be LIKE '%' + @BillingEmail + '%' otherwise you are looking for an email address that contain the string '@BillingEmail' instead of the contents of the variable.

Unfortunately when you attempt a "contains" search e.g. like '%somesearchterm% SQL Server cannot make use of indexes to narrow down the search and therefore has to read and check every record in the table being searched. Therefore it will be fairly slow.

This kind of generalised search can be quite difficult to get to perform well. However while having an index doesn't narrow down the rows that need to be searched, having an index on say BillingEmail does reduce the amount of data that SQL Server has to load in order to perform the search and can make quite a dramatic improvement in performance. It also means the table itself isn't locked while you are searching.

So you could consider adding 3 indexes, BillingEmail, BillingFirstName, BillingLastName. You need to customise this to the way your system works, but if for example people normally search on FirstName and LastName you can use a single index on both columns.

This isn't very scalable if you want to search on many columns. You can extend the idea by creating a combined field e.g. FullName which is updated on a trigger to FirstName + ' ' + LastName and then you can index FullName.

When you actually carry out the search it can often perform better to do a first search on one search term and store the results (or just the IDs) in a temp table and then perform further filter actions against the temp table. This is especially useful if you can get your users to in some way narrow down the search with an initial selection.

Full-Text Indexes are also an option.

Upvotes: 2

Ed Bangga
Ed Bangga

Reputation: 13006

No problem on your query except those using of variables with wild cards. And also, I suggest not to use on LIKE operator in ON clause. This will just give additional Cost on query execution.

 LEFT JOIN [Test].[dbo].[Address] a on a.Id = o.BillingAddressId
    WHERE (@BillingEmail IS null OR a.[Email] LIKE '%'+@BillingEmail+'%') 
    AND (@BillingFirstName IS null OR a.[FirstName] LIKE '%'+@BillingFirstName+'%')
    AND (@BillingLastName IS null OR a.[LastName] LIKE '%'+@BillingLastName+'%')

Upvotes: 1

Related Questions