Joe the Business Intelligence Guy

A journey through the design and delivery of business intelligence solutions.

TSQL Handling Runtime Comparison Type Support

 

I thought I’d share a rediscovery of an old technique I have used to permit more flexible queries. Enjoy! You can extend this concept to permit combinations of from/thru ranges as well. Note that I am also using some SQL 2008 syntax for inline declaration and setting of variables

  (DECLARE @Var AS {DATATYPE} = ‘Value’)

Runtime Comparison Type Support

In order to support providing the user with the ability to determine what comparison is needed along with the value to be used for the comparison, two parameters will be required.

The first parameter allows the user to specify the comparison that they want. For numeric values this would include greater than, less than, equal to comparisons. For character values this would include equal to and like comparisons.

The second parameter allows the user to specify the value to be used for the comparison selected.

LIKE Usage Notes:

The LIKE comparison operator is primarily useful for character string comparisons. LIKE Determines whether or not a given character string matches a specified pattern. A pattern can include regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the character string; wildcard characters, however, can be matched with arbitrary fragments of the character string.

Using wildcard characters makes the LIKE operator more flexible than using the = and != string comparison operators. If any of the arguments are not of character string data type, Microsoft® SQL Server™ converts them to character string data type, if possible.

 LIKE Wildcard Characters:

% = Any string of zero or more characters.

WHERE title LIKE%computer%finds all book titles with the word ‘computer’ anywhere in the book title.

_ (underscore) = Any single character.

WHERE au_fname LIKE ‘_ean’ finds all four-letter first names that end with ean (Dean, Sean, and so on).

[ ] = Any single character within the specified range ([a-f]) or set ([abcdef]).

WHERE au_lname LIKE ‘[C-P]arsen’ finds author last names ending with arsen and beginning with any single character between C and P, for example Carsen, Larsen, Karsen, and so on.

[^] = Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

WHERE au_lname LIKE ‘de[^l]%all author last names beginning with de and where the following letter is not l.

*/

— NOTES:

— Using SQL 2008 DECLARE and SET inline syntax change to add separate SET statements for pre SQL 2008

— Using AdventureWorks2008 sample database

*/

— Numeric Comparison Supports >, <, and =

DECLARE @Operator AS VARCHAR(4) = ‘<‘

DECLARE @Value AS NUMERIC(38,6) = 20000.00

 

— Character Comparison Supports = and LIKE

–DECLARE @Operator2 AS VARCHAR(4) = ‘=’

–DECLARE @Value2 AS NVARCHAR(25) = ‘CE1F-4E31-89’

DECLARE @Operator2 AS VARCHAR(4) = ‘Like’

DECLARE @Value2 AS NVARCHAR(25) = ‘C%’

 

SELECT [SalesOrderID]

      ,[SalesOrderDetailID]

      ,[CarrierTrackingNumber]

      ,[OrderQty]

      ,[ProductID]

      ,[SpecialOfferID]

      ,[UnitPrice]

      ,[UnitPriceDiscount]

      ,[LineTotal]

      ,[rowguid]

      ,[ModifiedDate]

  FROM [AdventureWorks2008].[Sales].[SalesOrderDetail]

  WHERE

    ((@Operator = ‘>’ AND [LineTotal] > @Value)

    OR (@Operator = ‘<‘ AND [LineTotal] < @Value)

    OR (@Operator = ‘=’ AND [LineTotal] = @Value))

    AND

    ((@Operator2 = ‘=’ AND [CarrierTrackingNumber] = @Value2)

    OR (@Operator2 = ‘Like’ AND [CarrierTrackingNumber] LIKE @Value2))

GO

Advertisements

Written by Joe Salvatore

March 11, 2009 at 1:59 am

%d bloggers like this: