Bill - Query
Query a bill object in QuickBooks Online using SQL Server
- See SQLHTTP easy setup for QuickBooks
- 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 35 36 37 38 39 40 41 42 43 |
CREATE PROCEDURE usp_QuickBooks_v3_CompanyBill_Query( @Profile nvarchar(100), @Response nvarchar(MAX) OUTPUT, @StatusCode int OUTPUT, @StatusDescription nvarchar(MAX) OUTPUT) AS DECLARE @URL nvarchar(MAX) DECLARE @QueryString varchar(MAX) DECLARE @HTTPSessionID uniqueidentifier DECLARE @SelectStatement varchar(MAX) SET @URL = 'https://sandbox-quickbooks.api.intuit.com/v3' EXEC SQLHTTP.net.UrlBuilder @URL OUTPUT, @Profile, 'company', '#RealmID','query' SET @SelectStatement = 'SELECT * FROM Bill' EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT, @Profile, 'query',@SelectStatement SET @URL = @URL + @QueryString EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC usp_QuickBooks_v3_Auth_Header @Profile, @HTTPSessionID EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @URL, @Method = 'GET', @ContentType = 'text/plain', @Accept = '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 |
DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) EXEC usp_QuickBooks_v3_CompanyBill_Query @Profile = 'My Quickbooks', @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/QueryResponse/Bill/JsonObject' SELECT T.C.value(N'@Id', N'nvarchar(MAX)') AS [Id] ,T.C.value(N'@DueDate', N'nvarchar(MAX)') AS [DueDate] ,T.C.value(N'@TotalAmt', N'nvarchar(MAX)') AS [TotalAmt] FROM @X.nodes(N'/JsonObject/QueryResponse/Bill/JsonObject') T(C) END |
|
1 2 3 4 5 6 7 8 |
Id DueDate TotalAmt --------- ------------- ------------- 1 2017-03-31 2000.00 105 2017-02-20 56.50 126 2017-05-14 205.00 |
- Account – Create
- Account – Fetch
- Account – Query
- Account – Update
- Attachable – Create
- Attachable – Delete
- Attachable – Fetch
- Attachable – Query
- Attachable – Update
- BatchRequest – Create
- Bill – Create
- Bill – Delete
- Bill – Fetch
- Bill – Query
- Bill – Update
- BillPayment – Create
- BillPayment – Delete
- BillPayment – Fetch
- BillPayment – Query
- BillPayment – Update
- Budget – Query
- Change Data Capture – Query
- Class – Create
- Class – Fetch
- Class – Query
- Class – Update
- CompanyInfo – Fetch
- CompanyInfo – Query
- CreditMemo – Create
- CreditMemo – Delete
- CreditMemo – Fetch
- CreditMemo – Query
- CreditMemo – Update
- Customer – Create
- Customer – Fetch
- Customer – Query
- Customer – Update
- Customer – Update (Sparse)
- Department – Create
- Department – Fetch
- Department – Query
- Department – Update
- Deposit – Create
- Deposit – Delete
- Deposit – Fetch
- Deposit – Query
- Deposit – Update
- Deposit – Update (Sparse)
- Employee – Create
- Employee – Fetch
- Employee – Query
- Employee – Update
- Estimate – Create
- Estimate – Delete
- Estimate – Fetch
- Estimate – Query
- Estimate – Send
- Estimate – Update
- Estimate – Update (Sparse)
- Invoice – Create
- Invoice – Delete
- Invoice – Fetch
- Invoice – Query
- Invoice – Send
- Invoice – Update
- Invoice – Update (Sparse)
- Item – Create
- Item – Fetch
- Item – Inactivate
- Item – Query
- Item – Update
- JournalEntry – Create
- JournalEntry – Delete
- JournalEntry – Fetch
- JournalEntry – Query
- JournalEntry – Update
- JournalEntry – Update (Sparse)
- Payment – Create
- Payment – Delete
- Payment – Fetch
- Payment – Query
- PaymentMethod – Create
- PaymentMethod – Fetch
- PaymentMethod – Query
- PaymentMethod – Update
- Preferences – Query
- Preferences – Read
- Preferences – Update
- Purchase – Create
- Purchase – Delete
- Purchase – Fetch
- Purchase – Query
- Purchase – Update
- Purchase Order – Update
- PurchaseOrder – Create
- PurchaseOrder – Delete
- PurchaseOrder – Fetch
- PurchaseOrder – Query
- RefundReceipt – Create
- RefundReceipt – Delete
- RefundReceipt – Fetch
- RefundReceipt – Query
- RefundReceipt – Update
- RefundReceipt – Update (Sparse)
- SalesReceipt – Create
- SalesReceipt – Delete
- SalesReceipt – Fetch
- SalesReceipt – Query
- SalesReceipt – Send
- SalesReceipt – Update
- SalesReceipt – Update (Sparse)
- TaxAgency – Create
- TaxAgency – Fetch
- TaxAgency – Query
- TaxCode – Fetch
- TaxCode – Query
- TaxRate – Fetch
- TaxRate – Query
- TaxService – Create
- Term – Create
- Term – Fetch
- Term – Query
- Term – Update
- TimeActivity – Create
- TimeActivity – Delete
- TimeActivity – Fetch
- TimeActivity – Query
- TimeActivity – Update
- Transfer – Create
- Transfer – Delete
- Transfer – Fetch
- Transfer – Query
- Transfer – Update
- Transfer – Update (Sparse)
- Vendor – Create
- Vendor – Fetch
- Vendor – Query
- Vendor – Update
- VendorCredit – Create
- VendorCredit – Delete
- VendorCredit – Fetch
- VendorCredit – Query
- VendorCredit – Update
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.