Blog posts tagged with 'SQL'

In the Rate Expression of an Option (or OptionExit) type record, put in a SQL statement (or call a stored procedure).  It must return a table of Shipping Options - columns: Name VARCHAR, Description VARCHAR, Rate Decimal. 

For example, if you can set up Warehouses as locations for local Pickup:

SELECT w.Name,
       Address1
         + case when isnull(address2,'') = '' then '' else ' ' + Address2 end
         + ', ' + a.City + ', ' + s.Abbreviation + ' ' + ZipPostalCode  as Description,
       0.00 as Rate
  FROM Warehouse w
  JOIN Address a ON a.Id = w.AddressId
  JOIN StateProvince s on s.Id = a.StateProvinceId;

The customer sees: 

If an expression starts with one of these keywords, then the expression is evaluated as a SQL query:

            SELECT

            ?SELECT"

            EXECUTE"

            ?EXECUTE

Note that there are two forms of SELECT and EXECUTE; one form is prefixed with a “?”.  Without the leading “?” the query is treated as a standard SQL paramaterized query using “@”-prefixed parameters.  For example:

select Zone from ShippingZone_Origin23235 where @p1 between PrefixFrom and PrefixTo; [ZipCode3]

 This default behavior should be used when there is a concern for SQL injection.  (It is also recommended to use ErrorExit to test for for such possibilities.)

With the leading “?” the query can contain zero-based indexed placeholders (format items to be used in String.Format).  For example:

?select Zone from ShippingZone_Origin{0} where '{1}' between PrefixFrom and PrefixTo;[OriginZipCode],[ZipCode3]

This provides the ability to create truly dynamic queries.  In the above example, the table name contains a format specifier.

The result of a SQL expression needs to be a single scalar value (i.e. a String, a Decimal, etc.).  Typically the expression is assigned to a variable.

An example of using SQL queries would be to do a custom rate lookup from a set of Zone/Weight/Rate  tables in the database.  The first table would be used to lookup a Zone base on a shipping destination zip code, and the second table would use the looked up Zone and the total weight of items in the cart to lookup the rate.

The first option is to use parameterized queries (“select” without the “?” prefix).  In this example, a constant Origin Zip Code is provided, but this could instead be a calculation of a specific warehouse based on what is in the cart.

The second option is to use the “?” prefix so that the table name can be dynamically determined.  In this example, a constant Origin Zip Code is provided, but this could instead be a caclulation of a specific warehous based on what is in the cart. 

SQL queries using zero-based index placeholders

Here's an example stored procedure.  Note that the SELECT statements only return a single column (and should only return a single row)

CREATE PROCEDURE [dbo].[ShippingZone_LookupRate]
(
    @zip     VARCHAR(9),
    @weight  DECIMAL(8,4)
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @zone VARCHAR(3);
    IF @WEIGHT = 0 
      SET @weight = 1;

    select @zone = Zone from ShippingZone_Origin23235 where SUBSTRING(@zip,1,3) between PrefixFrom and PrefixTo

    IF      @zone = '2' select Zone2 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '3' select Zone3 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '4' select Zone4 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '5' select Zone5 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '6' select Zone6 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '7' select Zone7 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '8' select Zone8 from ShippingZone_Rate where Weight = Ceiling(@weight)
    ELSE IF @zone = '9' select Zone9 from ShippingZone_Rate where Weight = Ceiling(@weight)    
END