Orders - Batch Operations
Batch Create, Update and Delete WooCommerce Orders using SQL Server
- See SQLHTTP easy setup for WooCommerce
- See API Call documentation for parameter values and other information
- Create the stored procedure documented below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
CREATE PROCEDURE usp_WooCommerce_v2_Orders_Batch_Update( @Profile nvarchar(100), @Body varchar(MAX), @Response nvarchar(MAX) OUTPUT, @StatusCode int OUTPUT, @StatusDescription nvarchar(MAX) OUTPUT) AS DECLARE @URL nvarchar(MAX) DECLARE @HTTPSessionID uniqueidentifier SET @URL = SQLHTTP.net.AuthParam(@Profile, 'RootURL') EXEC SQLHTTP.net.UrlBuilder @URL OUTPUT, @Profile, 'wp-json', 'wc', 'v2', 'orders', 'batch' EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC usp_WooCommerce_v2_Auth_Header @HTTPSessionID, @Profile, @URL, 'POST' EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @URL, @Method = 'POST', @Body = @Body, @ContentType = 'application/json', @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 |
DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) DECLARE @Body varchar(MAX) SET @Body = '{ "create": [ { "payment_method": "bacs", "payment_method_title": "Direct Bank Transfer", "billing": { "first_name": "John", "last_name": "Doe", "address_1": "969 Market", "address_2": "", "city": "San Francisco", "state": "CA", "postcode": "94103", "country": "US", "email": "john.doe@example.com", "phone": "(555) 555-5555" }, "shipping": { "first_name": "John", "last_name": "Doe", "address_1": "969 Market", "address_2": "", "city": "San Francisco", "state": "CA", "postcode": "94103", "country": "US" }, "line_items": [ { "product_id": 78, "quantity": 1 }, { "product_id": 110, "quantity": 1 }, { "product_id": 104, "variation_id": 118, "quantity": 1 } ], "shipping_lines": [ { "method_id": "flat_rate", "method_title": "Flat Rate", "total": 30 } ] }, { "payment_method": "bacs", "payment_method_title": "Direct Bank Transfer", "set_paid": true, "billing": { "first_name": "John", "last_name": "Doe", "address_1": "969 Market", "address_2": "", "city": "San Francisco", "state": "CA", "postcode": "94103", "country": "US", "email": "john.doe@example.com", "phone": "(555) 555-5555" }, "shipping": { "first_name": "John", "last_name": "Doe", "address_1": "969 Market", "address_2": "", "city": "San Francisco", "state": "CA", "postcode": "94103", "country": "US" }, "line_items": [ { "product_id": 104, "variation_id": 117, "quantity": 7 } ], "shipping_lines": [ { "method_id": "flat_rate", "method_title": "Flat Rate", "total": 20 } ] } ], "update": [ { "id": 169, "shipping_methods": "Local Delivery" } ], "delete": [ 168 ] }' EXEC usp_WooCommerce_v2_Orders_Batch_Update @Profile = 'My WooCommerce', @Body = @Body, @Response = @Response OUTPUT, @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT IF @StatusCode >= 400 EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response ELSE BEGIN DECLARE @X xml SET @X = SQLHTTP.net.Json_To_Xml(@Response, '0') --The XPath syntax below was easily generated by executing the following commands: --EXEC SQLHTTP.net.XQueryHelper @X --and then executing: --EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject/create' -- --EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject/delete' -- --EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject/update' -- SELECT 'create' AS [type] ,T.C.value(N'JsonObject[1]/@id', N'nvarchar(MAX)') AS [id] ,T.C.value(N'JsonObject[1]/@status', N'nvarchar(MAX)') AS [status] ,T.C.value(N'JsonObject[1]/@total', N'nvarchar(MAX)') AS [total] ,T.C.value(N'JsonObject[1]/error[1]/@message', N'nvarchar(MAX)') AS [message] FROM @X.nodes(N'/JsonObject/create') T(C) UNION SELECT 'update' AS [type] ,T.C.value(N'JsonObject[1]/@id', N'nvarchar(MAX)') AS [id] ,T.C.value(N'JsonObject[1]/@status', N'nvarchar(MAX)') AS [status] ,T.C.value(N'JsonObject[1]/@total', N'nvarchar(MAX)') AS [total] ,T.C.value(N'JsonObject[1]/error[1]/@message', N'nvarchar(MAX)') AS [message] FROM @X.nodes(N'/JsonObject/update') T(C) UNION SELECT 'delete' AS [type] ,T.C.value(N'JsonObject[1]/@id', N'nvarchar(MAX)') AS [id] ,T.C.value(N'JsonObject[1]/@status', N'nvarchar(MAX)') AS [status] ,T.C.value(N'JsonObject[1]/@total', N'nvarchar(MAX)') AS [total] ,T.C.value(N'JsonObject[1]/error[1]/@message', N'nvarchar(MAX)') AS [message] FROM @X.nodes(N'/JsonObject/delete') T(C) END |
|
1 2 3 4 5 6 7 8 |
type id status total message -------- ------- ------------ ----------- ---------------- create 170 pending 61.99 NULL create 171 processing 151.99 NULL delete 168 completed 63.98 NULL update 169 processing 60.00 NULL |
- Attribute – Create
- Attribute – Delete
- Attribute – Fetch
- Attribute – Update
- Attribute Term – Create
- Attribute Term – Delete
- Attribute Term – Fetch
- Attribute Term – Update
- Attribute Terms – Batch Operations
- Attribute Terms – Fetch
- Attributes – Batch Operations
- Attributes – Fetch
- Categories – Batch Operations
- Categories – Fetch
- Category – Create
- Category – Delete
- Category – Fetch
- Category – Update
- Coupon – Create
- Coupon – Delete
- Coupon – Fetch
- Coupon – Update
- Coupons – Batch Operations
- Coupons – Fetch
- Customer – Create
- Customer – Delete
- Customer – Fetch
- Customer – Update
- Customer Downloads – Fetch
- Customers – Batch Operations
- Customers – Fetch
- Order – Create
- Order – Delete
- Order – Fetch
- Order – Update
- Order Note – Create
- Order Note – Delete
- Order Note – Fetch
- Order Notes – Fetch
- Orders – Batch Operations
- Orders – Fetch
- Payment Gateway – Fetch
- Payment Gateway – Update
- Payment Gateways – Fetch
- Product – Create
- Product – Delete
- Product – Fetch
- Product – Update
- Products – Batch Operations
- Products – Fetch
- Refund – Create
- Refund – Delete
- Refund – Fetch
- Refunds – Fetch
- Reports – Fetch
- Review – Fetch
- Reviews – Fetch
- Sales Report – Fetch
- Setting Option – Fetch
- Setting Option – Update
- Setting Options – Batch Operations
- Setting Options – Fetch
- Settings Groups – Fetch
- Shipping Class – Create
- Shipping Class – Delete
- Shipping Class – Fetch
- Shipping Class – Update
- Shipping Classes – Batch Oprations
- Shipping Classes – Fetch
- Shipping Method – Fetch
- Shipping Methods – Fetch
- Shipping Zone – Create
- Shipping Zone – Delete
- Shipping Zone – Fetch
- Shipping Zone – Update
- Shipping Zone Method – Create
- Shipping Zone Method – Delete
- Shipping Zone Method – Fetch
- Shipping Zone Method – Update
- Shipping Zone Methods – Fetch
- Shipping Zones – Fetch
- System Status Tool – Fetch
- System Status Tool – Run
- System Status Tools – Fetch
- Tag – Create
- Tag – Delete
- Tag – Fetch
- Tag – Update
- Tags – Batch Operations
- Tags – Fetch
- Tax Class – Create
- Tax Class – Delete
- Tax Classes – Fetch
- Tax Rate – Create
- Tax Rate – Delete
- Tax Rate – Fetch
- Tax Rate – Update
- Tax Rates – Batch Operations
- Tax Rates – Fetch
- Top Sellers Report – Fetch
- Variation – Create
- Variation – Delete
- Variation – Fetch
- Variation – Update
- Variations – Batch Operations
- Variations – Fetch
- Webhook – Create
- Webhook – Delete
- Webhook – Fetch
- Webhooks – Batch Operations
- Webhooks – Fetch
IMPORTANT DISCLAIMER
CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.
USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.
USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.