# SYSPRO SQL Query for Inventory On Hand by Warehouse

> Source: https://netray.co/erp-help/syspro-sql-inventory-on-hand-by-warehouse-query
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: ERP Help

## Short answer

SYSPRO runs on SQL Server with one database per company, typically named like SysproCompanyA, and inventory is split between InvMaster for part-level data and InvWarehouse for quantities per warehouse. Join them on StockCode, filter, and read the quantity columns. Use a read-only login, never write to SYSPRO tables directly, and prefer e.net business objects for any change.

## Kind

advanced

## System

small-erp

## Erp

SYSPRO

## Module

SQL Server / Reporting

## Query

SYSPRO SQL query inventory on hand by warehouse

## Alternate Queries

- SYSPRO InvMaster InvWarehouse join
- SYSPRO SQL table names inventory
- SYSPRO read database directly safe
- SYSPRO company database SQL report

## Applies To

SYSPRO 7 and SYSPRO 8 on Microsoft SQL Server; table and column names should be confirmed against the data dictionary for your exact release and any customisations

## Steps

### How to build a safe inventory query

**Items:**

- Ask your DBA for a SQL login or Windows group with db_datareader on the company database only. Do not use the SYSPRO application account.
- Identify the right company database. Company ids such as A and B usually map to databases SysproCompanyA and SysproCompanyB, with shared administration data in a separate admin database.
- Start from InvMaster for descriptions, stock status and unit of measure, then join InvWarehouse on StockCode for per-warehouse quantities.
- Filter by warehouse and by stock status. Remember that obsolete or inactive stock codes can still carry quantity.
- Add the quantity columns you need, typically on hand and allocated, and calculate available as the difference rather than expecting a stored column.
- Use READ UNCOMMITTED or snapshot isolation for reporting queries on busy systems, and understand the dirty-read caveat.
- Compare a sample to the Inventory Query screen in SYSPRO to confirm you read the columns correctly.
- Save the query as a view or a SYSPRO Reporting Services dataset rather than leaving it in a personal script folder.

## Sections

### The core join

**Body:**

InvMaster holds one row per stock code, with description, product class, stocking unit of measure, and status flags. InvWarehouse holds one row per stock code and warehouse. The join is by StockCode. Quantities live on the warehouse row, so a part stocked in three warehouses returns three rows.

Aggregating across warehouses with GROUP BY StockCode gives company-wide on hand, but remember that in-transit and consigned stock may be tracked separately. Do not assume that the sum equals what finance values.

**Code:**

-- Confirm column names against your release's data dictionary
USE SysproCompanyA;

SELECT m.StockCode, m.Description, w.Warehouse,
       w.QtyOnHand, w.QtyAllocated,
       w.QtyOnHand - w.QtyAllocated AS QtyAvailable
FROM InvMaster AS m
JOIN InvWarehouse AS w ON w.StockCode = m.StockCode
WHERE w.Warehouse = 'FG'
  AND w.QtyOnHand <> 0
ORDER BY m.StockCode;

### Why not write to the tables

**Body:**

SYSPRO business logic, such as costing, ledger integration, movement records, allocation and audit trails, lives in the application and its business objects, not in database constraints. A direct UPDATE on InvWarehouse changes a number without creating the matching movement or ledger entry, and the inventory sub-ledger no longer reconciles. Vendors generally treat direct writes as unsupported.

For any change, use the e.net business objects or the SYSPRO client programs. For reads, direct SQL is normal and widely used, provided the load is controlled.

### Performance and locking

**Body:**

Reporting queries that scan large tables during shift changes or MRP runs compete with SYSPRO transactions. Add predicates on indexed columns, avoid SELECT star across wide tables, and schedule heavy extracts out of hours or against a replica or restored copy.

READ UNCOMMITTED avoids blocking but can return half-posted data. For finance-grade numbers, run on a consistent snapshot or a backup restored to a reporting server.

### Customisations and custom forms

**Body:**

Many sites add user-defined fields or custom tables. Those are not in the standard dictionary, so document them. When the application is upgraded, standard tables can change shape, so keep queries in views with a short list of dependencies so you know what to retest.

## Pitfalls

- Using a read-write login for reporting and one day running an UPDATE by mistake.
- Assuming available stock is a stored column when it is derived from on hand, allocations and sometimes reservations.
- Querying the wrong company database and reconciling against the wrong ledger.
- Treating obsolete stock codes as irrelevant and missing the quantity still on the shelf.
- Running unfiltered scans during MRP or period-end and slowing every user.
- Copying a query from another release without checking that the column still exists.

## Ai Angle

An assistant grounded in SYSPRO translates a question such as which finished goods have stock below the reorder point in the main warehouse into SQL against InvMaster and InvWarehouse, shows the query it ran, runs it through a read-only account, and respects the operator's SYSPRO role. That is the pattern behind ERPray: queries are visible and inspectable, and any change is a separate approved action.

## Faq

### Is it safe to query the SYSPRO database directly?

**Answer:** Reading is common and safe if you use a read-only login and control the load. Writing directly is not recommended and often unsupported, because it bypasses costing, ledger integration and audit. Use business objects for changes.

### Where do I find the table and column definitions?

**Answer:** SYSPRO publishes a data dictionary and schema information with each release, and you can also inspect INFORMATION_SCHEMA in SQL Server. Use those for your version rather than trusting examples online, since columns can be added or renamed between releases.

### Which database holds the company data?

**Answer:** Each SYSPRO company has its own SQL Server database, commonly SysproCompanyA, SysproCompanyB and so on, plus an administration database for operators and system-wide settings. Names are set during installation, so confirm with your DBA.

### Can I use these queries in Power BI or SSRS?

**Answer:** Yes. Create views over the tables, point the report tool at a read-only connection, and schedule refreshes outside peak hours. SYSPRO also offers its own reporting and analytics tools, which may be simpler for standard operational reports.

## Search terms

SYSPRO SQL query, SYSPRO InvMaster, SYSPRO InvWarehouse, SYSPRO database tables, SYSPRO inventory report SQL, SYSPRO company database, SYSPRO Power BI, SYSPRO read only SQL

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json