Today we’re going to go through the process of designing, building and testing a SQL Stored Procedure (SP) that will build dynamic search queries based on the best practices described by SQL MVP and Microsoft Certified Master Gail Shaw in her SQL in the Wild blog entry entitled Catch-all Queries. A more in-depth article on the same subject can be found in SQL MVP Erland Sommarskog’s blog entitled Dynamic Search Conditions in T-SQL. While we will not explore all of the aspects described by Mr. Sommarskog, a thorough reading of both articles is highly recommended.
This article is designed as a beginner’s guide on the subject, based on my personal experience developing SPs of this nature. It walks you through the steps that I have used successfully to ensure that the SP not only delivers the expected results but does so with high performance. Following the steps I suggest will help to ensure that you can deliver on such an assignment quickly and with minimal issues arising.
The Business Scenario
Pretty much everybody in the world has heard of FedEx, but if you’ve never shipped a package with them perhaps you’ve heard of TNT or DHL. While I don’t work for any of those big three, one of my customers is a partially-owned subsidiary of TNT and I’ve done work for other express distribution companies, as these businesses are classified.
Every shipment, or consignment as they’re called in the industry, goes through a life cycle while being handled by the express distribution company. An abridged version of that life cycle is shown below.
Prior to arrival at a Distribution Center (DC), a courier may pick up your consignment, which may consist of multiple packages. Pickups are not included in this life cycle as they are tracked separately. We’ll assume that each consignment is assigned a unique number upon arrival at the DC. After arrival, each package is weighed and its “dims” (dimensions) are recorded. This is essential for rating the consignment. At the time of Manifest Data Entry, additional information is captured about the consignment, such as who is the customer, where was it picked up and where it is to be delivered. Along with this information, upon saving the consignment is rated. The consignment is then moved to the destination DC (a process that we will mostly ignore) and ultimately delivered. Each of these can be tracked as an event in the consignment’s life cycle with a status code, for example:
- SIP – Station in Processing is recorded upon arrival at any DC.
- WGT – Packages are weighed.
- MDE – Manifest Data Entry.
- OPS – A status code that is used to record a comment that could occur anywhere during the life cycle of a consignment. We noted that we’re basically going to ignore the “Move to Destination” processing, which consists of SOPs (Station out Processing) and SIPs as well as a variety of other status codes, and we will simulate these (possibly multiple) moves with OPS.
- POD – Proof of Delivery occurs once a consignment is delivered and the consignee (the recipient) signs for it.
After POD, a consignment may go through additional steps in a life cycle that moves through invoicing and payment. In the express business, a Key Performance Indicator (KPI) is delivery by a commitment date/time, so packages that are “pending POD” or are delivered late are of specific interest. Each of the events described, with the exception of OPS, must occur in a consignment’s life cycle for it to be considered “closed.”
Status codes, often referred to as “scans” because bar-coding is prevalent, are either at the package level (SIP and WGT) or at the consignment level (all others). We’ll record consignment level scans as being for package number zero.
Test Data and Tables Setup
Our greatly simplified data setup will consist of 3 tables that we’ll populate in this order:
- Customers – includes customer name and customer type. The full customer repository should contain billing and shipping addresses (likely to be child tables), and probably rate (child) tables that are specific to that customer, but these are omitted for simplicity.
- Shipments – for holding our consignments. We will be dealing with only a single package for each consignment and showing just a very few of the columns that normally appear on a shipment record.
- Shipment_History – for holding each event that occurs in a shipment’s history. Again, many actual columns are omitted. We’ve included only sufficient information to understand the example SP we’re constructing.
We won’t create one separately but of course there would normally be a table of Status Codes, which we have described in the prior section.
It is time to open up your favorite SQL sandbox so you can download and run the Express Data Setup.sql provided in the resources file. The Data Definition Language (DDL) for our simplified tables is shown below. All sample code provided will run in SQL Server 2008 or later.
CREATE TABLE Customers
(
CustID VARCHAR(20) NOT NULL
, Customer_Name NVARCHAR(100) NOT NULL
, Customer_Type CHAR(1) NOT NULL
CHECK ( Customer_Type IN ( 'A', 'R' ) )
, PRIMARY KEY CLUSTERED ( CustID )
);
CREATE TABLE Shipments
(
Consignment_No VARCHAR(30) NOT NULL
, ReferenceNo VARCHAR(30) NULL
, CustID VARCHAR(20) NULL
FOREIGN KEY REFERENCES Customers ( CustID )
, Total_Pkgs INT NOT NULL
, Last_Status VARCHAR(12) NULL
, Last_Status_DT DATETIME NULL
, PRIMARY KEY CLUSTERED ( Consignment_No )
);
CREATE TABLE Shipment_History
(
Consignment_No VARCHAR(30)
NOT NULL
FOREIGN KEY REFERENCES Shipments ( Consignment_No )
, Status_DT DATETIME NOT NULL
, Status_Code VARCHAR(12) NOT NULL
, Pkg_No INT NOT NULL
DEFAULT ( 0 )
, PRIMARY KEY CLUSTERED ( Consignment_No, Status_Code, Status_DT, Pkg_No )
);
GONow we need to populate our tables with some sample data we’ll use to test our dynamic search procedure. The data has been strategically designed to facilitate our testing, and we’ll describe that in more detail later. For now you can refer to the comments.
-- Two regular and one premium (A) type customers
INSERT INTO Customers
VALUES ( 'ACME', 'ACME Building Supplies', 'R' ) ,
( 'ATLAS', 'Atlas Engineering Inc.', 'R' ) ,
( 'APPLE', 'Apple Computers', 'A' );
-- 8 consignments - two for each customer and 2 that have not been
-- manifest data entered so have not yet been assigned a customer
-- ReferenceNo refers to a customer's internal document ID number
INSERT INTO Shipments
( Consignment_No, ReferenceNo, CustID, Total_Pkgs )
VALUES ( '8033701', 'PO 12345', 'ACME', 1 ) ,
( '0152037', NULL, 'ACME', 1 ) ,
( '4292860', NULL, 'ATLAS', 1 ) ,
( '0806473', 'INV 43523', 'ATLAS', 1 ) ,
( '6289811', NULL, 'APPLE', 1 ) ,
( '0642191', 'SO 1111', 'APPLE', 1 ) ,
( '8363496', NULL, NULL, 1 ) ,
( '7271931', NULL, NULL, 1 );
-- Some history records that are complete, some that are in progress
-- and some that are complete (POD) but missing some required events
INSERT INTO Shipment_History
( Consignment_No, Status_Code, Status_DT, Pkg_No )
VALUES ( '8033701', 'SIP', '2013-10-19 22:19', 1 ) ,
( '8033701', 'WGT', '2013-10-20 18:47', 1 ) ,
( '8033701', 'MDE', '2013-10-21 10:47', 0 ) ,
( '8033701', 'OPS', '2013-10-21 21:43', 0 ) ,
( '8033701', 'POD', '2013-10-22 02:44', 0 ) ,
( '0152037', 'SIP', '2013-10-23 00:31', 1 ) ,
( '0152037', 'WGT', '2013-10-23 17:03', 1 ) ,
( '0152037', 'MDE', '2013-10-23 20:12', 0 ) ,
( '4292860', 'SIP', '2013-10-23 21:00', 1 ) ,
( '4292860', 'MDE', '2013-10-23 22:06', 0 ) ,
( '4292860', 'POD', '2013-10-24 10:54', 0 ) ,
( '0806473', 'SIP', '2013-10-25 04:16', 1 ) ,
( '0806473', 'MDE', '2013-10-25 08:49', 0 ) ,
( '6289811', 'SIP', '2013-10-25 14:28', 1 ) ,
( '6289811', 'WGT', '2013-10-26 05:35', 1 ) ,
( '6289811', 'OPS', '2013-10-26 20:08', 0 ) ,
( '6289811', 'POD', '2013-10-27 14:45', 0 ) ,
( '0642191', 'SIP', '2013-10-28 06:06', 1 ) ,
( '0642191', 'MDE', '2013-10-28 13:11', 0 ) ,
( '8363496', 'SIP', '2013-10-29 00:36', 1 ) ,
( '7271931', 'SIP', '2013-10-29 04:01', 1 ) ,
( '7271931', 'WGT', '2013-10-29 11:37', 1 );
SELECT * FROM Shipments;The SQL to create and populate this data also contains a TRIGGER. We’re not here today to discuss TRIGGERs, but the reason we’ve included it is that examination of the Shipments table indicates that there are two columns in that table that are de-normalized out of the Shipment_History table: Last_Status and Last_Status_DT. We’ve done this for performance reasons (“Normalize till it hurts, de-normalize till it works”) and the TRIGGER will populate that data for us when we load the Shipment_History table.
The final SELECT in the SQL above retrieves these results, showing us that our TRIGGER has done its job:
Consignment_No ReferenceNo CustID Total_Pkgs Last_Status Last_Status_DT 0152037 NULL ACME 1 MDE 2013-10-23 20:12:00.000 0642191 SO 1111 APPLE 1 MDE 2013-10-28 13:11:00.000 0806473 INV 43523 ATLAS 1 MDE 2013-10-25 08:49:00.000 4292860 NULL ATLAS 1 POD 2013-10-24 10:54:00.000 6289811 NULL APPLE 1 POD 2013-10-27 14:45:00.000 7271931 NULL NULL 1 WGT 2013-10-29 11:37:00.000 8033701 PO 12345 ACME 1 POD 2013-10-22 02:44:00.000 8363496 NULL NULL 1 SIP 2013-10-29 00:36:00.000
Note the ReferenceNo field populated for some customers, which contains an internal number the customer would recognize.
The Dynamic Search Requirements
In our User Interface (UI) we have a form that we’ll call Shipment Tracking. On this form are a number of criteria including date range, consignment number, reference number, etc. The user may enter some or all of these criteria; if more than one is entered these are combined using AND.
- The user may want to search within a date range, which would apply to the last status date column.
- The user may want to directly retrieve a specific consignment number.
- The user may want to search on a customer’s reference number, but since that field on the manifest data entry form is free-form, a LIKE match is desired.
- The user may want to search all consignments for a specific customer or all consignments for a specific type of customer (A or R).
- Since tracking that a shipment has achieved all of the required steps in the life cycle is important, history needs to be examined in order to find things like:
- Shipments pending POD (no POD as the last status in history).
- All shipments that have been MDE’d, even if that is not the last status code.
- All shipments that have not been MDE’d.
- All shipments that have a POD but were not weighed (WGT).
In order to handle the last requirement, our UI form will allow entry of 2 status codes. The first can be searched as either “in history” or “last in history,” while the other will support “missing from history.” Either or both can be provided.
For purposes of our demonstration, we’ll only return the columns defined in our Shipments table.
Planning the Queries
The next important step is to examine the querying requirements and create a series of example queries that exercise the various scenarios. This is necessary because we’ll ultimately build these queries so they can be executed as dynamic SQL, as suggested in the quoted SQL in the Wild blog at the beginning of this article. You can follow along and copy/paste each query from the article or you can download the Express Sample Queries.sql in the resources section of this article.
We’ll start with a base query, which if the user has entered a date range will look something like this, assuming we’re using best practice to query within a date range:
-- Base query
SELECT Consignment_No
, ReferenceNo
, CustID
, Total_Pkgs
, Last_Status
, Last_Status_DT
FROM dbo.Shipments
WHERE Last_Status_DT >= '2013-10-25'
AND Last_Status_DT < '2013-10-29';We’ll talk more about dates entered through the UI in a few minutes.
The queries on consignment and reference numbers are equally undistinguished, appearing as follows given that usually when querying on these factors the date range is not important. Each of these queries will return some results from our sample data.
-- Query on a consignment number
SELECT Consignment_No
, ReferenceNo
, CustID
, Total_Pkgs
, Last_Status
, Last_Status_DT
FROM dbo.Shipments
WHERE Consignment_No = '6289811';
-- Query on a customer's reference number
SELECT Consignment_No
, ReferenceNo
, CustID
, Total_Pkgs
, Last_Status
, Last_Status_DT
FROM dbo.Shipments
WHERE ReferenceNo LIKE '%23%';
Queries on a specific customer number can be done directly off the Shipments table, however when querying on a customer type, a JOIN is required back to the Customers table.
-- Query on a customer ID
SELECT Consignment_No
, ReferenceNo
, CustID
, Total_Pkgs
, Last_Status
, Last_Status_DT
FROM dbo.Shipments
WHERE CustID = 'APPLE';
-- Query on a customer type
SELECT a.Consignment_No
, a.ReferenceNo
, a.CustID
, a.Total_Pkgs
, a.Last_Status
, a.Last_Status_DT
FROM dbo.Shipments a
JOIN dbo.Customers b
ON a.CustID = b.CustID
WHERE b.Customer_Type = 'R';The last query illustrates an important point. Whenever your dynamic SQL needs to JOIN tables, it is best to assign each table an alias (I personally like short aliases) and then qualify each referenced column name with the appropriate alias to ensure unambiguous name recognition.
If we need to query on “last status,” that too is quite easy and it is why we de-normalized that data from the Shipment_History table as this query is quite common.
-- Query on last status in history
SELECT a.Consignment_No
, a.ReferenceNo
, a.CustID
, a.Total_Pkgs
, a.Last_Status
, a.Last_Status_DT
FROM dbo.Shipments a
WHERE a.Last_Status = 'POD';Querying on a status code “in history” or “missing from history” is not quite as simple, but not that hard either, as illustrated by the following examples.
-- Query on status (MDE) in history
SELECT a.Consignment_No
, a.ReferenceNo
, a.CustID
, a.Total_Pkgs
, a.Last_Status
, a.Last_Status_DT
FROM dbo.Shipments a
WHERE EXISTS ( SELECT 1
FROM dbo.Shipment_History b
WHERE a.consignment_no = b.consignment_no
AND b.status_code = 'MDE' );
-- Query on status (WGT) missing from history
SELECT a.Consignment_No
, a.ReferenceNo
, a.CustID
, a.Total_Pkgs
, a.Last_Status
, a.Last_Status_DT
FROM dbo.Shipments a
WHERE NOT EXISTS ( SELECT 1
FROM dbo.Shipment_History b
WHERE a.consignment_no = b.consignment_no
AND b.status_code = 'WGT' );Of course, the above two WHERE clauses can be combined to find “shipments with MDE but no WGT” as follows:
-- Query for consignments with MDE that have not been weighed
SELECT a.Consignment_No
, a.ReferenceNo
, a.CustID
, a.Total_Pkgs
, a.Last_Status
, a.Last_Status_DT
FROM dbo.Shipments a
WHERE EXISTS ( SELECT 1
FROM dbo.Shipment_History b
WHERE a.consignment_no = b.consignment_no
AND b.status_code = 'MDE' )
AND NOT EXISTS ( SELECT 1
FROM dbo.Shipment_History b
WHERE a.consignment_no = b.consignment_no
AND b.status_code = 'WGT' );We’ll leave it as an exercise for the enthralled reader, to not only verify that each query returns results, but that these results are correct.
Planning the Stored Procedure
Now that we have carefully analyzed and planned for the queries we will need to construct, it is equally essential to plan for how our SP will be constructed. Specifically we need to determine the call signature and what (if any) data cleansing we need to perform on its supplied arguments.
My philosophy when it comes to these things is to keep the UI as simple as possible. Do as little validation as is needed to make the form work. Yes, you do want the form to only capture valid status codes (easy with dropdown lists) and customer IDs. Likewise, only valid dates should be allowed. Perhaps an audit that requires a date range if consignment or reference number is not supplied. But there is no need to go into the complexities of what is only allowed with what else. Just form those into a query and if the return results have no rows, it is because the user supplied filtering criteria that was too restrictive.
On the other hand, we all know that those pesky .NET developers are not nearly as good at programming as us SQL-ers are! So make sure that all parameters being passed into your SP at least pass the sniff test – that is they’re reasonably valid. You may want to throw some back at the front end with an error message, or simply process them into something you can use (I generally prefer the latter).
Let’s look at the input parameters we’ll need to meet the business requirements:
- Start and/or End dates to apply to Last_Status_DT (again, more on this in a minute)
- Consignment Number
- Reference Number
- Customer ID
- Customer Type
- Status Code (in or last in history)
- Status Code (missing from history)
With this we can write the skeleton for our SP:
CREATE PROCEDURE Shipment_Tracking
(
@StartDT DATETIME = NULL
, @EndDT DATETIME = NULL
, @Consignment_No VARCHAR(30) = NULL
, @ReferenceNo VARCHAR(30) = NULL
, @CustID VARCHAR(20) = NULL
, @Customer_Type CHAR(1) = NULL
, @Status_Code VARCHAR(12) = NULL
, @StatusType TINYINT = NULL -- 0=NA, 1=Last in history, 2=In history
, @MissingStatus VARCHAR(12) = NULL
, @debug TINYINT = 0 -- 1=Display debug results
)
AS
BEGIN
SET NOCOUNT ON;
-- Our logic will be here
ENDThe parameters we didn’t mention are:
- @StatusType – which tells us whether @Status_Code is to be the last or simply in the consignment’s history.
- @debug – which we’ll use to display debugging information during development.
Each of our input parameters is initially assigned a value so that none is required, thus calling the SP without any parameters should result in all consignments being returned.
After doing this one or two times, you’ll come to realize that the easiest way to trigger constructing your dynamic SQL is to use the presence of a parameter value (NOT NULL) to trigger adding bits to the dynamic SQL. So one of our first data cleansing procedures will be to make sure that any character string passed to us is truly NULL if intended to be. To do this we’ll need a SELECT that looks like this:
SELECT @Consignment_No = NULLIF(@Consignment_No, '')
, @ReferenceNo = NULLIF(@ReferenceNo, '')
, @CustID = NULLIF(@CustID, '')
, @Customer_Type = NULLIF(@Customer_Type, '')
, @Status_Code = NULLIF(@Status_Code, '')
, @MissingStatus = NULLIF(@MissingStatus, '');Now we finally get to talk about start and end dates. In the front end, it may be possible that a @StartDT is provided without an @EndDT, in which case the design means “give me any consignment with a last status on @StartDT.” Likewise, when a user enters a date range like 2013-10-25 to 2013-10-28, it is a pretty good bet they really mean up through the end of the day on 28 October. Obviously, we need to drop any time components from our date. We can do this in a couple of really simple steps.
-- Truncate time component from start date
DECLARE @StartDate DATETIME = DATEADD(day, 0, DATEDIFF(day, 0, @StartDT))
-- Truncate time component from end date and add 1
, @EndDate DATETIME = DATEADD(day, 1, DATEDIFF(day, 0, @EndDT));
-- If @EndDate is NULL, then use @StartDT + 1
SELECT @EndDate = ISNULL(@EndDate, DATEADD(day, 1, DATEDIFF(day, 0, @StartDT)));There are two remaining parameter cleansing opportunities:
- To be sure that @debug is either 0 or 1.
- To be sure that @StatusType is 0 if @Status_Code is NULL (and that it is one of the permissible values = 0, 1, 2).
SELECT @debug = CASE WHEN @debug IS NULL THEN 0
WHEN @debug IN ( 0, 1 ) THEN @debug
ELSE 0
END
-- If @StatusType NOT IN (1, 2) then @Status_Code will be ignored
, @StatusType = CASE WHEN NULLIF(@Status_Code, '') IS NULL THEN 0
WHEN @StatusType IN ( 0, 1, 2 ) THEN @StatusType
ELSE 0
END;If it happens that @StatusType gets passed in as zero (or not IN (1,2)), then any @Status_Code that gets passed in will be ignored. All of this can be combined with our SP signature to give us this:
CREATE PROCEDURE Shipment_Tracking
(
@StartDT DATETIME = NULL
, @EndDT DATETIME = NULL
, @Consignment_No VARCHAR(30) = NULL
, @ReferenceNo VARCHAR(30) = NULL
, @CustID VARCHAR(20) = NULL
, @Customer_Type CHAR(1) = NULL
, @Status_Code VARCHAR(12) = NULL
, @StatusType TINYINT = NULL -- 0=NA, 1=Last in history, 2=In history
, @MissingStatus VARCHAR(12) = NULL
)
AS
BEGIN
SET NOCOUNT ON;
-- Truncate time component from start date
DECLARE @StartDate DATETIME = DATEADD(day, 0,
DATEDIFF(day, 0, @StartDT))
-- Truncate time component from end date and add 1
, @EndDate DATETIME = DATEADD(day, 1, DATEDIFF(day, 0, @EndDT));
-- If @EndDate is NULL, then use @StartDT + 1
SELECT @EndDate = ISNULL(@EndDate,
DATEADD(day, 1, DATEDIFF(day, 0, @StartDT)))
, @Consignment_No = NULLIF(@Consignment_No, '')
, @ReferenceNo = NULLIF(@ReferenceNo, '')
, @CustID = NULLIF(@CustID, '')
, @Customer_Type = NULLIF(@Customer_Type, '')
, @Status_Code = NULLIF(@Status_Code, '')
, @MissingStatus = NULLIF(@MissingStatus, '')
-- If @StatusType NOT IN (1, 2) then @Status_Code will be ignored
, @StatusType = CASE WHEN NULLIF(@Status_Code, '') IS NULL
THEN 0
WHEN @StatusType IN ( 0, 1, 2 )
THEN @StatusType
ELSE 0
END;
IF @debug = 1
BEGIN
PRINT CONVERT(VARCHAR(20), @StartDate, 120);
PRINT CONVERT(VARCHAR(20), @EndDate, 120);
END
ENDNote that all our SP does at this point is to clean up the input parameters and print the @StartDate and @EndDate. You should take a moment to ensure that these input parameters are handled the way we expect them to be.
EXEC Shipment_Tracking
@StartDT = '2013-10-13 14:00'
,@debug = 1;
EXEC Shipment_Tracking
@StartDT = '2013-10-13 14:00'
,@EndDT = '2013-10-15 15:00'
,@debug = 1;Constructing the Dynamic SQL
To use the system stored procedure to execute the SQL we need to DECLARE a couple of variables. The @SQLParms variable is of interest at the moment, and coincidentally it look quite a lot like the parameters in our call signature, with just @StatusType and @debug removed.
DECLARE @SQL NVARCHAR(MAX)
, @SQLParms NVARCHAR(MAX) = N'
@StartDT DATETIME
,@EndDT DATETIME
,@Consignment_No VARCHAR(30)
,@ReferenceNo VARCHAR(30)
,@CustID VARCHAR(20)
,@Customer_Type CHAR(1)
,@Status_Code VARCHAR(12)
,@MissingStatus VARCHAR(12)';
EXEC sp_executesql @SQL, @SQLParms;Obviously, if we were to execute this now nothing much would happen. We need to construct our @SQL string first. Note that using sp_executesql is strongly recommended, rather than EXEC (@SQL), because it will avoid issues that might occur due to SQL injection.
We’ll start with our base query, to which we will in a moment add the requisite JOINs and WHERE clauses.
SELECT @SQL = N'
SELECT a.Consignment_No, a.ReferenceNo, a.CustID, a.Total_Pkgs
,a.Last_Status, a.Last_Status_DT
FROM dbo.Shipments a
WHERE 1=1';If we were to PRINT @SQL, it would look quite nicely formatted, just like the rest of the queries we’ve presented. Now is where I get on my soapbox so if you don’t want to hear my diatribe about nicely formatting the SQL you write, skip the next two paragraphs and continue.
A friend named Chris Morris once suggested that when writing SQL: “Make it work, make it fast and then make it pretty.” Truer words were never spoken, unfortunately I see a lot of people skipping the last one or two steps. Nicely formatted SQL, with consistent indentation, is much easier for the ordinary mortal to process and understand. I really hate it when I have to wade through what I’d call “stream of consciousness” or write-only SQL. This is when it is appropriate to reinstate firing squads as appropriate punishment.
One of the steps we’ll be doing is what is called “bench-checking;” where we review the SQL we’re building to make sure that it is probably going to work when we run it. Having the SQL in our @SQL variable nicely formatted is going to help us with that. When it comes to formatting, it doesn’t much matter whether you indent with 4 characters or tabs, where you choose to place your indents, etc. What matters is that you put some line breaks and indents in consistently, so choose something that works for you and stick to it. Please give the poor schmuck that comes along after you a break, will you?
Now that we’ve gotten past that, let’s look at our base query. What in the world is up with that WHERE 1=1 thing? I call that the WHERE clause seed. Each of our filtering criteria needs to add AND (something) to narrow the filtering criteria. Using WHERE 1=1 allows us to always have a WHERE clause even when no filtering criteria is passed. So now we just need to add to it. Let’s start by adding our date filtering criteria.
SELECT @SQL = N' SELECT a.Consignment_No, a.ReferenceNo, a.CustID, a.Total_Pkgs ,a.Last_Status, a.Last_Status_DT FROM dbo.Shipments a WHERE 1=1'
+ CHAR(10)
+ CASE WHEN @StartDate IS NOT NULL
THEN ' AND a.Last_Status_DT >= @StartDT ' + CHAR(10)
+ ' AND a.Last_Status_DT < @EndDT ' + CHAR(10)
ELSE ''
END;
IF @debug = 1
PRINT @SQL;Note how we’ve added leading spaces and the CHAR(10) delimiter at the end of any lines where we want to cause a line break in the printed SQL. This will be displayed in the messages pane as:
SELECT a.Consignment_No, a.ReferenceNo, a.CustID, a.Total_Pkgs
,a.Last_Status, a.Last_Status_DT
FROM dbo.Shipments a
WHERE 1=1
AND a.Last_Status_DT >= @StartDT
AND a.Last_Status_DT < @EndDTWe’re not working with really big queries here so this method of PRINTing the SQL will work just fine. However if you find yourself generating massive queries, you can use the method described by SQL MVP Jeff Moden to Display More Than 8000 Characters. For our small queries with nicely embedded CHAR(10) line breaks, you can also change the PRINT statement to something like this, which uses the super-fast, community string splitter also championed by Jeff: DelimitedSplit8K (which you can download from that link).
IF @debug = 1 SELECT [LineNo]=RIGHT(10000+ItemNumber, 4), [SQL]=Item FROM dbo.DelimitedSplit8K(@SQL, CHAR(10));
This displays the SQL in a SSMS Results pane as this.
LineNo SQL 0001 0002 SELECT a.Consignment_No, a.ReferenceNo, a.CustID, a.Total_Pkgs 0003 ,a.Last_Status, a.Last_Status_DT 0004 FROM dbo.Shipments a 0005 WHERE 1=1 0006 AND a.Last_Status_DT >= @StartDT 0007 AND a.Last_Status_DT < @EndDT 0008
These line numbers will even correspond to any errors reported when you execute the T-SQL! I don’t know about you, but that works for me.
Now let’s add to our query the code to filter by consignment/reference numbers and customer ID.
SELECT @SQL = N'
SELECT a.Consignment_No, a.ReferenceNo, a.CustID, a.Total_Pkgs
,a.Last_Status, a.Last_Status_DT
FROM dbo.Shipments a
WHERE 1=1' + CHAR(10)
+ CASE WHEN @StartDate IS NOT NULL
THEN ' AND a.Last_Status_DT >= @StartDT ' + CHAR(10)
+ ' AND a.Last_Status_DT < @EndDT ' + CHAR(10)
ELSE ''
END
+ CASE WHEN @Consignment_No IS NOT NULL
THEN ' AND a.Consignment_No = @Consignment_No '
+ CHAR(10)
ELSE ''
END
+ CASE WHEN @ReferenceNo IS NOT NULL
THEN ' AND a.ReferenceNo LIKE ''%'' + @ReferenceNo + ''%'''
+ CHAR(10)
ELSE ''
END
+ CASE WHEN @CustID IS NOT NULL
THEN ' AND a.CustID = @CustID ' + CHAR(10)
ELSE ''
END;All are pretty straightforward, but you may want to note how we added the wildcard delimiter (%) to the LIKE filter on reference number. For cases where you have lots of quotes you need to embed, consider using QUOTENAME.
As you add each of these bits to the dynamic SQL, you should run it through the SP in debug mode and bench check the results.
Our penultimate step is to add the conditions that will support filtering by our status codes and whether they’re in, last or missing from history. Referring back to the sample queries we constructed, there is one case where the Last_Status_Code column on the Shipments record is the filter criteria and the other two WHERE conditions were EXISTS and NOT EXISTS within the specified sub-query.
SELECT @SQL = N'
SELECT a.Consignment_No, a.ReferenceNo, a.CustID, a.Total_Pkgs
,a.Last_Status, a.Last_Status_DT
FROM dbo.Shipments a
WHERE 1=1' + CHAR(10)
+ CASE WHEN @StartDate IS NOT NULL
THEN ' AND a.Last_Status_DT >= @StartDT ' + CHAR(10)
+ ' AND a.Last_Status_DT < @EndDT ' + CHAR(10)
ELSE ''
END
+ CASE WHEN @Consignment_No IS NOT NULL
THEN ' AND a.Consignment_No = @Consignment_No '
+ CHAR(10)
ELSE ''
END
+ CASE WHEN @ReferenceNo IS NOT NULL
THEN ' AND a.ReferenceNo LIKE ''%'' + @ReferenceNo + ''%'''
+ CHAR(10)
ELSE ''
END
+ CASE WHEN @CustID IS NOT NULL
THEN ' AND a.CustID = @CustID ' + CHAR(10)
ELSE ''
END
+ CASE WHEN @StatusType = 1
AND @Status_Code IS NOT NULL
THEN ' AND a.Last_Status = @Status_Code ' + CHAR(10)
ELSE ''
END
+ CASE WHEN @StatusType = 2
AND @Status_Code IS NOT NULL
THEN ' AND EXISTS ' + CHAR(10) + ' ('
+ CHAR(10) + ' SELECT 1' + CHAR(10)
+ ' FROM dbo.Shipment_History b' + CHAR(10)
+ ' WHERE a.consignment_no = b.consignment_no AND'
+ CHAR(10)
+ ' b.status_code = @Status_Code'
+ CHAR(10) + ' )' + CHAR(10)
ELSE ''
END
+ CASE WHEN @MissingStatus IS NOT NULL
THEN ' AND NOT EXISTS ' + CHAR(10) + ' ('
+ CHAR(10) + ' SELECT 1' + CHAR(10)
+ ' FROM dbo.Shipment_History b' + CHAR(10)
+ ' WHERE a.consignment_no = b.consignment_no AND'
+ CHAR(10)
+ ' b.status_code = @MissingStatus'
+ CHAR(10) + ' )' + CHAR(10)
ELSE ''
END;Finally, and I do recommend that you do this last, we’ll add the JOIN clause that allows searching on customer type along with the filtering criteria on b.Customer_Type. With a little more formatting, the final SELECT to create our @SQL variable looks like this:
SELECT @SQL = N'
SELECT a.Consignment_No, a.ReferenceNo, a.CustID, a.Total_Pkgs
,a.Last_Status, a.Last_Status_DT
FROM dbo.Shipments a' + CHAR(10) +
-- Add the INNER JOIN when necessary
CASE WHEN @Customer_Type IS NOT NULL
THEN ' INNER JOIN dbo.Customers b ON a.CustID = b.CustID' + CHAR(10)
ELSE '' END + ' WHERE 1=1' + CHAR(10) +
CASE WHEN @StartDate IS NOT NULL
THEN ' AND a.Last_Status_DT >= @StartDT ' + CHAR(10) +
' AND a.Last_Status_DT < @EndDT ' + CHAR(10)
ELSE '' END +
CASE WHEN @Consignment_No IS NOT NULL
THEN ' AND a.Consignment_No = @Consignment_No ' + CHAR(10)
ELSE '' END +
CASE WHEN @ReferenceNo IS NOT NULL
THEN ' AND a.ReferenceNo LIKE ''%'' + @ReferenceNo + ''%''' + CHAR(10)
ELSE '' END +
CASE WHEN @CustID IS NOT NULL
THEN ' AND a.CustID = @CustID ' + CHAR(10)
ELSE '' END +
CASE WHEN @StatusType = 1 AND @Status_Code IS NOT NULL
THEN ' AND a.Last_Status = @Status_Code ' + CHAR(10)
ELSE '' END +
-- Also add the customer type filtering
CASE WHEN @Customer_Type IS NOT NULL
THEN ' AND b.Customer_Type = @Customer_Type' + CHAR(10)
ELSE '' END +
CASE WHEN @StatusType = 2 AND @Status_Code IS NOT NULL
THEN ' AND EXISTS ' + CHAR(10) +
' (' + CHAR(10) +
' SELECT 1' + CHAR(10) +
' FROM dbo.Shipment_History b' + CHAR(10) +
' WHERE a.consignment_no = b.consignment_no AND' + CHAR(10) +
' b.status_code = @Status_Code' + CHAR(10) +
' )' + CHAR(10)
ELSE '' END +
CASE WHEN @MissingStatus IS NOT NULL
THEN ' AND NOT EXISTS ' + CHAR(10) +
' (' + CHAR(10) +
' SELECT 1' + CHAR(10) +
' FROM dbo.Shipment_History b' + CHAR(10) +
' WHERE a.consignment_no = b.consignment_no AND' + CHAR(10) +
' b.status_code = @MissingStatus' + CHAR(10) +
' )' + CHAR(10)
ELSE '' END;Now we just need to pass a few parameters to our sp_executesql and we’re ready to start testing!
EXEC sp_executesql @SQL
,@SQLParms
,@StartDT = @StartDate
,@EndDT = @EndDate
,@Consignment_No = @Consignment_No
,@ReferenceNo = @ReferenceNo
,@CustID = @CustID
,@Customer_Type = @Customer_Type
,@Status_Code = @Status_Code
,@MissingStatus = @MissingStatus;Note how it was necessary to pass our adjusted @StartDate and @EndDate to the corresponding parameters we define in the dynamic SQL. Our final SP looks like this:
CREATE PROCEDURE Shipment_Tracking
(
@StartDT DATETIME = NULL
, @EndDT DATETIME = NULL
, @Consignment_No VARCHAR(30) = NULL
, @ReferenceNo VARCHAR(30) = NULL
, @CustID VARCHAR(20) = NULL
, @Customer_Type CHAR(1) = NULL
, @Status_Code VARCHAR(12) = NULL
, @StatusType TINYINT = NULL
-- 0=NA, 1=Last in history, 2=In history
, @MissingStatus VARCHAR(12) = NULL
, @debug TINYINT = 0
-- 1=Display debug results
)
AS
BEGIN
SET NOCOUNT ON;
-- Truncate time component from start date
DECLARE @StartDate DATETIME = DATEADD(day, 0,
DATEDIFF(day, 0, @StartDT))
-- Truncate time component from end date and add 1
, @EndDate DATETIME = DATEADD(day, 1, DATEDIFF(day, 0, @EndDT))
, @SQL NVARCHAR(MAX)
, @SQLParms NVARCHAR(MAX) = N' @StartDT DATETIME ,@EndDT DATETIME ,@Consignment_No VARCHAR(30) ,@ReferenceNo VARCHAR(30) ,@CustID VARCHAR(20) ,@Customer_Type CHAR(1) ,@Status_Code VARCHAR(12) ,@MissingStatus VARCHAR(12)'; -- If @EndDate is NULL, then use @StartDT + 1 SELECT @EndDate = ISNULL(@EndDate, DATEADD(day, 1, DATEDIFF(day, 0, @StartDT))) ,@Consignment_No = NULLIF(@Consignment_No, '') ,@ReferenceNo = NULLIF(@ReferenceNo, '') ,@CustID = NULLIF(@CustID, '') ,@Customer_Type = NULLIF(@Customer_Type, '') ,@Status_Code = NULLIF(@Status_Code, '') ,@MissingStatus = NULLIF(@MissingStatus, '') ,@debug = CASE WHEN @debug IS NULL THEN 0 WHEN @debug IN (0,1) THEN @debug ELSE 0 END -- If @StatusType NOT IN (1, 2) then @Status_Code will be ignored ,@StatusType = CASE WHEN NULLIF(@Status_Code, '') IS NULL THEN 0 WHEN @StatusType IN (0, 1, 2) THEN @StatusType ELSE 0 END;
IF @debug = 1
BEGIN
PRINT CONVERT(VARCHAR(20), @StartDate, 120);
PRINT CONVERT(VARCHAR(20), @EndDate, 120);
END
SELECT @SQL = N' SELECT a.Consignment_No, a.ReferenceNo, a.CustID, a.Total_Pkgs ,a.Last_Status, a.Last_Status_DT FROM dbo.Shipments a'
+ CHAR(10)
+ CASE WHEN @Customer_Type IS NOT NULL
THEN ' INNER JOIN dbo.Customers b ON a.CustID = b.CustID'
+ CHAR(10)
ELSE ''
END + ' WHERE 1=1' + CHAR(10)
+ CASE WHEN @StartDate IS NOT NULL
THEN ' AND a.Last_Status_DT >= @StartDT '
+ CHAR(10)
+ ' AND a.Last_Status_DT < @EndDT '
+ CHAR(10)
ELSE ''
END
+ CASE WHEN @Consignment_No IS NOT NULL
THEN ' AND a.Consignment_No = @Consignment_No '
+ CHAR(10)
ELSE ''
END
+ CASE WHEN @ReferenceNo IS NOT NULL
THEN ' AND a.ReferenceNo LIKE ''%'' + @ReferenceNo + ''%'''
+ CHAR(10)
ELSE ''
END
+ CASE WHEN @CustID IS NOT NULL
THEN ' AND a.CustID = @CustID ' + CHAR(10)
ELSE ''
END
+ CASE WHEN @StatusType = 1
AND @Status_Code IS NOT NULL
THEN ' AND a.Last_Status = @Status_Code '
+ CHAR(10)
ELSE ''
END
+ CASE WHEN @Customer_Type IS NOT NULL
THEN ' AND b.Customer_Type = @Customer_Type'
+ CHAR(10)
ELSE ''
END
+ CASE WHEN @StatusType = 2
AND @Status_Code IS NOT NULL
THEN ' AND EXISTS ' + CHAR(10)
+ ' (' + CHAR(10)
+ ' SELECT 1' + CHAR(10)
+ ' FROM dbo.Shipment_History b'
+ CHAR(10)
+ ' WHERE a.consignment_no = b.consignment_no AND'
+ CHAR(10)
+ ' b.status_code = @Status_Code'
+ CHAR(10) + ' )' + CHAR(10)
ELSE ''
END
+ CASE WHEN @MissingStatus IS NOT NULL
THEN ' AND NOT EXISTS ' + CHAR(10)
+ ' (' + CHAR(10)
+ ' SELECT 1' + CHAR(10)
+ ' FROM dbo.Shipment_History b'
+ CHAR(10)
+ ' WHERE a.consignment_no = b.consignment_no AND'
+ CHAR(10)
+ ' b.status_code = @MissingStatus'
+ CHAR(10) + ' )' + CHAR(10)
ELSE ''
END;
IF @debug = 1
SELECT [LineNo] = RIGHT(10000 + ItemNumber, 4)
, [SQL] = Item
FROM dbo.DelimitedSplit8K(@SQL, CHAR(10));
EXEC sp_executesql @SQL, @SQLParms, @StartDT = @StartDate,
@EndDT = @EndDate, @Consignment_No = @Consignment_No,
@ReferenceNo = @ReferenceNo, @CustID = @CustID,
@Customer_Type = @Customer_Type, @Status_Code = @Status_Code,
@MissingStatus = @MissingStatus;
ENDThis appears in the Express Final Search SP.sql resource provided.
Testing the Stored Procedure
Before we get into actually testing our SP, we need to circle back for a moment to the test data we used to populate our tables. This was not randomly generated nor did it magically appear by the grace of Lord Buddha. Rest assured that it never does.
When I need test data and when I can, I love using Excel to create it for me. While you can also generate gobs of test data directly in SQL (I often do it for performance test harnesses), Excel offers a lot of flexibility to develop very specific test cases and do so very quickly. Before we look at that Excel, let’s take a look at our Customers and Shipments.
- Since we need to query on customer type, we need to have at least two, thus we chose to create three customers so we could spread out those types.
- For Shipments, we decided we’d create two for each customer, but we also needed some shipments that had no customers so we created two of those also.
The spreadsheet we used looks as follows, although I added the highlighting at the end for illustration.
Consignment numbers were initially created using an Excel formula like:
=+RIGHT(CONCATENATE("0000000",ROUND(RAND()*1000000,0)),7)
Once we had them, we didn’t want them to change so we copied/pasted special (values) over the randomly generated consignment numbers.
The green lines show how we expanded the consignment numbers to multiple rows so we could construct some relevant history. Note how the random dates appearing in column F are constantly increasing. We did this with cell formulas that also used RAND() and then (as with consignment numbers), pasted the values over the calculated cells to stop them from changing. Formatting of those cells to look like a SQL DATETIME value was helpful for checking purposes.
Each consignment history record is assigned status codes from the life cycle. All consignments start out with a SIP.
- 8033701, 0152037, 8363496 and 7271931 each contain every status code expected in the life cycle (up to a given point in time).
- 0152037, 8363496 and 7271931 (et al) have not completed their life cycle (are pending POD).
- 4292860, 0806473 and 0642191 are missing WGT but end at different status codes.
- 6289811 is missing MDE.
We not have some consignments where history is complete, some that end before the end of the life cycle and others that are in various stages of the life cycle but missing a scan. The PkgNo column can simply be filled in or created based on a formula.
Note the formula that is shown at the top of the display (outlined in hot pink). It appears in each cell circled in the red bordered cells in column I. I commonly use something like this to create the SQL that I’ll use to INSERT the data – in this case it is the column values that appear within the SQL INSERT’s VALUES clause in our opening data setup script. You still had to format the DATETIME using TEXT, but otherwise the formula is pretty straightforward.
While we were planning our queries, we constructed several that we can use for comparative testing against our SP. You should now run each of the queries you’ll find in the prior section “Planning Your Queries” against the equivalent EXEC SP, verifying that the results produced are the same. Here’s one for example:
-- Query for consignments with MDE that have not been weighed
SELECT a.Consignment_No, a.ReferenceNo, a.CustID, a.Total_Pkgs
,a.Last_Status, a.Last_Status_DT
FROM dbo.Shipments a
WHERE EXISTS
(
SELECT 1
FROM dbo.Shipment_History b
WHERE a.consignment_no = b.consignment_no AND b.status_code = 'MDE'
)
AND NOT EXISTS
(
SELECT 1
FROM dbo.Shipment_History b
WHERE a.consignment_no = b.consignment_no AND b.status_code = 'WGT'
);
EXEC Shipment_Tracking
@StatusType = 2
,@Status_Code = 'MDE'
,@MissingStatus = 'WGT'
,@debug = 1;You should also try various combinations, such as the following:
-- Query on a customer type + MDE in history
SELECT a.Consignment_No, a.ReferenceNo, a.CustID, a.Total_Pkgs
,a.Last_Status, a.Last_Status_DT
FROM dbo.Shipments a
JOIN dbo.Customers b ON a.CustID = b.CustID
WHERE b.Customer_Type = 'R'
AND EXISTS
(
SELECT 1
FROM dbo.Shipment_History b
WHERE a.consignment_no = b.consignment_no AND b.status_code = 'MDE'
);
EXEC Shipment_Tracking
@Customer_Type = 'R'
,@StatusType = 2
,@Status_Code = 'MDE'
,@debug = 1;Thorough testing in debug mode of many combinations will yield a dynamic search SP that is robust and useful.
What We Learned – Conclusions and Recap
In this article we learned to use best practice for constructing a dynamic search procedure including:
- Analyzing the business requirements.
- Planning the requisite queries.
- Planning the dynamic search stored procedure.
- Building the dynamic SQL to execute our search queries.
- Planning the test scenarios.
- Testing and debugging our stored procedure.
At the end of the Express Data Setup.sql resource you will find four DROP statements that you can use to clean up your sandbox (commented out).
This “how-to” article was based on development of a dynamic search SP quite similar to the specifications included herein, albeit with additional complications such as about 20 search criteria and 5 tables requiring JOINs under certain circumstances. Using this approach, the SP was fully developed and tested within just a few hours, so the techniques described will work for you should you decide to use them.
For those that would like a little experience gained through actually working through the example, try modifying the SP to improve its performance for one special case. If the user searches on “missing MDE,” that can be identified by simply looking for CustID IS NULL on the Shipments record.
We sincerely hope that this article helps beginners to form some well-crafted code for this scenario.
Dwain Camps
SQL Enthusiast
Skype: dwaincamps
