Creates an Order that can then be referenced as OrderId in a request to the Charge endpoint.

Stored Procedure Specific Information

You cannot create an order without IdempotencyKey and order items. To provide order items, you must either:

• insert a list of order items into a temporary table called 'OrderItem#TEMP' and then run an EXECUTE query referencing this temporary table, or
• supply a JSON aggregate value directly in a single EXECUTE query

Note: If you want to charge this order, you must set AutoCharge to 'true' and include either of the following in your query:

• a value for the CardNonce parameter (a payment token generated using Square payment SDKs), or
• values for the CustomerCardId and CustomerId parameters (to charge a customer's saved card on file)

Note:

• When AutoCharge is set to 'true', the driver uses Square's Payments API to immediately process the payment (authorization and capture). Authorization-only (delayed capture) is not supported.
• LocationId is required to execute this stored procedure. It must be provided either in the connection string or explicitly in the query. If not supplied, the request will fail.

Executing the Procedure Using Temporary Tables

Inserting order items into a temporary table allows you to break the execution of the CreateOrder stored procedure into smaller, less complex queries.

Populating the Temporary Table

Populate the temporary table using a series of INSERT queries.

INSERT INTO OrderItem#TEMP (Name, Quantity, BasePriceMoneyAmount, BasePriceMoneyCurrency) VALUES ('New York Strip Steak', '1', 1599, 'USD')
INSERT INTO OrderItem#TEMP (Name, Quantity, BasePriceMoneyAmount, BasePriceMoneyCurrency) VALUES ('New York Strip Steak-Special', '1', 2599, 'USD')
INSERT INTO OrderItem#TEMP (Quantity, CatalogObjectId, Modifiers, Discounts) VALUES ('2', 'BEMYCSMIJL46OCDV4KYIKXIB', '[\n" +
        "        {\n" +
        "          \"catalog_object_id\": \"CHQX7Y4KY6N5KINJKZCFURPZ\"\n" +
        "        }\n" +
        "      ]', '[\n" +
        "        {\n" +
        "          \"name\": \"Sale - $1.00 off\",\n" +
        "          \"amount_money\": {\n" +
        "            \"amount\": 100,\n" +
        "            \"currency\": \"USD\"\n" +
        "          }\n" +
        "        }\n" +
        "      ]')

You can insert into the following columns in the temporary table:

• Name
• Quantity
• BasePriceMoneyAmount
• BasePriceMoneyCurrency
• VariationName
• CatalogObjectId
• Modifiers
• Taxes
• Discounts

The last three columns are aggregates. For more information about the specific JSON structure, visit the Square API documentation.

Query Execution

After creating at least one order item, you can execute the stored procedure:

EXECUTE CreateOrder IdempotencyKey = '9777458c-9586-11e6-99f9-28cfe92138cf', AutoCharge = true, CardNonce = 'cnon:card-nonce-ok', ReferenceId = 'my-order-001', OrderLineItems = 'OrderItem#TEMP'

Note: To add more than one value to "Taxes" and "Discounts", you can use the OrderTaxes#TEMP and OrderDiscounts#TEMP tables. Please refer to the columns available for OrderTaxes and OrderDiscounts in the Orders View.

INSERT INTO OrderTaxes#TEMP (OrderTaxName, OrderTaxPercentage) VALUES ('State Sales Tax', '9')
INSERT INTO OrderTaxes#TEMP (OrderTaxName, OrderTaxPercentage) VALUES ('Central Sales Tax', '9')
INSERT INTO OrderDiscounts#TEMP (DiscountName, DiscountPercentage) VALUES ('Labor Day Sale', '5')
INSERT INTO OrderDiscounts#TEMP (DiscountName, DiscountPercentage) VALUES ('New Year Sale', '10')
EXECUTE CreateOrder IdempotencyKey = '9777458c-9586-11e6-99f9-28cfe92138cf', AutoCharge = true, CardNonce = 'cnon:card-nonce-ok', ReferenceId = 'my-order-001', OrderLineItems = 'OrderItem#TEMP', OrderTaxes = 'OrderTaxes#TEMP', OrderDiscounts = 'OrderDiscounts#TEMP'

Alternatively, you can manually provide a list of values for the OrderTaxes or OrderDiscounts aggregate columns using JSON structures.

EXECUTE CreateOrder IdempotencyKey = '9777458c-9586-11e6-99f9-28cfe92138cf', AutoCharge = true, CardNonce = 'cnon:card-nonce-ok', ReferenceId = 'my-order-001', OrderLineItems = 'OrderItem#TEMP', OrderTaxes = '[{"OrderTaxName":"State Sales Tax","OrderTaxPercentage": 9}]', OrderDiscounts = '[{"OrderDiscountName":"Labor Day Sale","OrderDiscountPercentage":5}]'

Executing the Procedure Using JSON Aggregates

As an alternative to populating a temporary table, you can create an order with a single EXECUTE query by specifying a list of order items in the OrderLineItems JSON aggregate column.

For example:

 
EXECUTE CreateOrder IdempotencyKey = '9777458c-9586-11e6-99f9-28cfe92138cf', AutoCharge = true, CardNonce = 'cnon:card-nonce-ok', ReferenceId = 'my-order-001', OrderLineItems = [{\"modifiers\": [{\"catalog_object_id\": \"CHQX7Y4KY6N5KINJKZCFURPZ\" }], \"name\" : \"New York Strip Steak\", \"quantity\" : \"1\", \"base_price_money\" : {\"amount\": 1599, \"currency\" : \"USD\" } }]

Input

Name	Type	Required	Description
AutoCharge	Boolean	False	If true, the order is immediately paid (authorized and captured) using Square's Payments API.
LocationId	String	False	The ID of the location associated with the order.
CardNonce	String	False	A payment source token (card nonce) generated by Square payment SDKs. This value is used as source_id in the Payments API.
CustomerCardId	String	False	The ID of a saved customer card on file. Used as source_id in the Payments API.
CustomerId	String	False	The ID of the customer to associate this payment with.
IdempotencyKey	String	True	A value you specify that uniquely identifies this order among orders you've created.
ReferenceId	String	False	An optional ID you can associate with the transaction for your own purposes.
OrderLineItems	String	True	The aggregate for the order line items.
OrderTaxAmountMoneyAmount	Integer	False	Only used for ad hoc taxes. The monetary amount of the tax. This column input will not be accepted if OrderTaxes column is added.
OrderTaxAmountMoneyCurrency	String	False	Only used for ad hoc taxes. The currency of the tax monetary amount. This column input will not be accepted if OrderTaxes column is added.
OrderTaxAppliedMoneyAmount	Integer	False	Only used for ad hoc taxes. The applied amount by the tax. This column input will not be accepted if OrderTaxes column is added.
OrderTaxAppliedMoneyCurrency	String	False	Only used for ad hoc taxes. The currency of the tax applied amount. This column input will not be accepted if OrderTaxes column is added.
OrderTaxCatalogObjectId	String	False	Only used for catalog taxes. The catalog object ID of an existing CatalogTax. This column input will not be accepted if OrderTaxes column is added.
OrderTaxName	String	False	Only used for ad hoc taxes. The tax's name. Do not provide a value for this field if you set catalogobjectid. This column input will not be accepted if OrderTaxes column is added.
OrderTaxPercentage	String	False	Only used for ad hoc taxes. The percentage of the tax, as a string representation of a decimal number. This column input will not be accepted if OrderTaxes column is added.
OrderTaxScope	String	False	Only used for ad hoc taxes. Indicates the level at which the tax applies. Possible values are OTHER_TAX_SCOPE, LINE_ITEM, ORDER. This column input will not be accepted if OrderTaxes column is added.
OrderTaxType	String	False	Only used for ad hoc taxes. The type of the tax. This column input will not be accepted if OrderTaxes column is added.
OrderTaxUID	String	False	Only used for ad hoc taxes. Unique ID that identifies the tax only within this order. This column input will not be accepted if OrderTaxes column is added.
OrderTaxes	String	False	The aggregate for the order taxes.
OrderDiscountAmountMoneyAmount	Integer	False	Only used for ad hoc discounts. The monetary amount of the discount. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscountAmountMoneyCurrency	String	False	Only used for ad hoc discounts. The currency of the discount monetary amount. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscountAppliedMoneyAmount	Integer	False	Only used for ad hoc discounts. The applied amount of the discount. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscountAppliedMoneyCurrency	String	False	Only used for ad hoc discounts. The currency of the discount applied amount. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscountCatalogObjectId	String	False	Only used for catalog discounts. The catalog object ID from exsiting CatalogDiscount. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscountName	String	False	Only used for ad hoc discounts. The discount's name. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscountPercentage	String	False	Only used for ad hoc discounts. The percentage of the discount, as a string representation of a decimal number. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscountScope	String	False	Only used for ad hoc discounts. Indicates the level at which the discount applies. Possible values are OTHER_DISCOUNT_SCOPE, LINE_ITEM, ORDER. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscountType	String	False	Only used for ad hoc discounts. The type of the discount. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscountUID	String	False	Only used for ad hoc discounts. Unique ID that identifies the discount only within this order. This column input will not be accepted if OrderDiscounts column is added.
OrderDiscounts	String	False	The aggregate for the order discounts.
SourceName	String	False	The origination details of the order.
State	String	False	The current state of the order.
PricingOptionsAutoApplyDiscounts	Boolean	False	The option to determine whether pricing rule-based discounts are automatically applied to an order.

Result Set Columns

Name	Type	Description
Success	Boolean	Whether the transaction was successful.
OrderId	String	The transaction's unique ID, issued by Square payments servers.
Charged	Boolean	Whether the order is charged or not.
PaymentId	String	The payment ID created after charging the order.