# SAP Business One Query Parameters Syntax Reference

> Source: https://netray.co/tools/sap-b1-query-parameters-and-query-tables-reference
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

The patterns for user prompts in B1 saved queries, for SQL Server and HANA, plus the tables that store queries and categories.

## Category

sap

## Erp

SAP Business One

## Audience

- SAP B1 consultant
- SAP B1 report writer
- SAP B1 power user

## Tool

**Kind:** worksheet

**Mode:** reference

**Columns:**

#### Pattern

**Key:** pattern

**Type:** text

#### SQL Server syntax

**Key:** mssql

**Type:** text

#### HANA syntax

**Key:** hana

**Type:** text

#### What the user sees

**Key:** user

**Type:** text

#### Notes

**Key:** notes

**Type:** text

**Rows:**

**Pattern:** Simple parameter

**Mssql:** SELECT T0.DocNum, T0.CardName FROM OINV T0 WHERE T0.DocDate >= [%0]

**Hana:** SELECT T0."DocNum", T0."CardName" FROM OINV T0 WHERE T0."DocDate" >= [%0]

**User:** A prompt labelled with the field description (Posting Date) and a date picker

**Notes:** B1 replaces [%0] with the value before running the SQL.

**Pattern:** Parameter on a key column

**Mssql:** WHERE T0.CardCode = [%0]

**Hana:** WHERE T0."CardCode" = [%0]

**User:** A prompt with a choose-from-list of business partners

**Notes:** B1 offers the list because it knows the column.

**Pattern:** Date range

**Mssql:** WHERE T0.DocDate BETWEEN [%0] AND [%1]

**Hana:** WHERE T0."DocDate" BETWEEN [%0] AND [%1]

**User:** Two date prompts

**Notes:** Number parameters in the order you want them asked.

**Pattern:** Same parameter used twice

**Mssql:** WHERE T0.DocDate >= [%0] OR T0.DocDueDate >= [%0]

**Hana:** WHERE T0."DocDate" >= [%0] OR T0."DocDueDate" >= [%0]

**User:** One prompt

**Notes:** A repeated number is asked once and reused.

**Pattern:** Several parameters, mixed fields

**Mssql:** WHERE T0.CardCode = [%0] AND T0.DocDate >= [%1] AND T0.DocStatus = [%2]

**Hana:** WHERE T0."CardCode" = [%0] AND T0."DocDate" >= [%1] AND T0."DocStatus" = [%2]

**User:** Three prompts in one window

**Notes:** Each prompt gets the field description as label.

**Pattern:** Comment header for variables

**Mssql:** /* SELECT FROM [dbo].[OINV] T0 */

**Hana:** Not used

**User:** Nothing; it tells B1 which table the parameters refer to

**Notes:** Needed on SQL Server when the parameter is assigned to a variable instead of compared in WHERE.

**Pattern:** Variable assigned from a parameter

**Mssql:** DECLARE @FromDate AS DATETIME /* WHERE */ SET @FromDate = /* T0.DocDate */ '[%0]'

**Hana:** Not available in a plain saved query: compare [%0] to the column directly

**User:** A Posting Date prompt

**Notes:** The comments /* WHERE */ and /* T0.DocDate */ give B1 the field for the prompt label and type.

**Pattern:** Variable used in the real query

**Mssql:** SELECT T0.DocNum, T0.DocTotal FROM OINV T0 WHERE T0.DocDate >= @FromDate

**Hana:** SELECT T0."DocNum", T0."DocTotal" FROM OINV T0 WHERE T0."DocDate" >= [%0]

**User:** Results after the prompt

**Notes:** Lets you reuse one value in subqueries, CTEs and UNIONs on SQL Server.

**Pattern:** Parameter with a SQL view

**Mssql:** /* SELECT FROM [dbo].[OCRD] T0 */ DECLARE @Card AS NVARCHAR(50) /* WHERE */ SET @Card = /* T0.CardCode */ '[%0]' SELECT * FROM dbo.ZV_OPEN_ITEMS V WHERE V.CardCode = @Card

**Hana:** SELECT * FROM "ZV_OPEN_ITEMS" V WHERE V."CardCode" = [%0]

**User:** A BP prompt

**Notes:** On SQL Server the comment header points B1 at a real table so the prompt works for a view. ZV_OPEN_ITEMS is an example name.

**Pattern:** Stored procedure

**Mssql:** /* SELECT FROM [dbo].[OINV] T0 */ DECLARE @d AS DATETIME /* WHERE */ SET @d = /* T0.DocDate */ '[%0]' EXEC dbo.Z_SALES_BY_DAY @d

**Hana:** CALL "Z_SALES_BY_DAY" ([%0])

**User:** A date prompt, then the procedure result

**Notes:** Procedure names are examples. The procedure must return one result set.

**Pattern:** Optional parameter

**Mssql:** WHERE (T0.SlpCode = @Slp OR @Slp = -1) with the DECLARE pattern

**Hana:** Not supported the same way; use separate saved queries

**User:** A prompt the user may fill with -1 for all

**Notes:** B1 prompts are mandatory by default; use a sentinel value.

**Pattern:** FMS and alerts

**Mssql:** $[$38.1.0] or $[OINV.CardCode] in formatted searches

**Hana:** Same $[...] syntax

**User:** No prompt; the value is taken from the open form

**Notes:** Query Manager uses [%0]; formatted searches use $[...]. Do not mix them.

**Pattern:** Saved query table

**Mssql:** SELECT IntrnalKey, QCategory, QName, QString FROM OUQR

**Hana:** SELECT "IntrnalKey", "QCategory", "QName", "QString" FROM OUQR

**User:** List of all saved queries with their SQL

**Notes:** QString holds the full SQL text. Use it to search queries that use a table or column.

**Pattern:** Query category table

**Mssql:** SELECT CategoryId, CatName FROM OQCN

**Hana:** SELECT "CategoryId", "CatName" FROM OQCN

**User:** Query Manager folders

**Notes:** Join OUQR.QCategory = OQCN.CategoryId.

**Pattern:** Find queries by table

**Mssql:** SELECT T0.QName, T1.CatName FROM OUQR T0 JOIN OQCN T1 ON T1.CategoryId = T0.QCategory WHERE T0.QString LIKE '%OITW%'

**Hana:** SELECT T0."QName", T1."CatName" FROM OUQR T0 JOIN OQCN T1 ON T1."CategoryId" = T0."QCategory" WHERE T0."QString" LIKE '%OITW%'

**User:** Every saved query that reads OITW

**Notes:** Useful before an upgrade or a table change.

**Pattern:** Authorization by category

**Mssql:** Not SQL: categories are assigned to authorization groups

**Hana:** Same

**User:** Users see only the categories their group allows

**Notes:** Set the group per category in Query Manager (Manage Categories) and grant the groups in General Authorizations.

**Pattern:** Read-only rule

**Mssql:** SELECT only

**Hana:** SELECT only

**User:** Do not run INSERT, UPDATE or DELETE

**Notes:** Direct writes to B1 tables are not supported by SAP; use the DI API or Service Layer.

**Notes:** Patterns follow SAP Business One 10.0 Query Manager and Query Generator behaviour and common SAP Community usage. The DECLARE pattern is a SQL Server technique; test it on your version. Object names starting with Z_ or ZV_ are examples.

## Content

**Intro:** This reference shows how to add user prompts to SAP Business One saved queries with [%0] parameters, including the DECLARE pattern for variables, date ranges, views and stored procedures. It covers SAP Business One 10.0 on SQL Server and SAP HANA, plus the OUQR and OQCN tables that store queries.

**Sections:**

#### How parameters work

**Body:** In Query Manager and Query Generator, [%0], [%1] and so on are placeholders. When the query runs, B1 looks at the column each placeholder is compared to, shows a prompt labelled with that field's description, offers a choose-from-list for known key columns and a date picker for dates, then substitutes the value into the SQL. The same number used twice is asked once. Because B1 needs a real column to build the prompt, the parameter must sit in a comparison with a table column in the FROM clause, or, on SQL Server, in the comment pattern that names one.

**Bullets:**

- Compare to a real column: T0.DocDate >= [%0].
- Number prompts in the order you want them asked.
- Formatted searches use $[...] instead of [%0].

#### The DECLARE pattern on SQL Server

**Body:** When you need a value in a variable, for a CTE, a UNION, a view or a procedure, write a comment header naming a real table, for example /* SELECT FROM [dbo].[OINV] T0 */, then DECLARE @FromDate AS DATETIME, then /* WHERE */, then SET @FromDate = /* T0.DocDate */ '[%0]'. The comments tell B1 which field the prompt belongs to so it shows Posting Date and a date picker, while SQL Server ignores them. After that, use @FromDate anywhere in the real query. On HANA saved queries this pattern is not available, so compare [%0] directly with the column.

#### Worked example: invoices for a customer and date range

**Body:** On SQL Server: SELECT T0.DocNum, T0.DocDate, T0.DocTotal FROM OINV T0 WHERE T0.CardCode = [%0] AND T0.DocDate BETWEEN [%1] AND [%2] ORDER BY T0.DocDate. The user gets a business partner prompt with a choose-from-list and two date pickers. On HANA the same query quotes the columns: T0."CardCode" = [%0] and T0."DocDate" BETWEEN [%1] AND [%2]. Save it in a category and set the category's authorization group so only the sales team sees it.

#### Where queries are stored

**Body:** Saved queries are in table OUQR: IntrnalKey is the key, QName the name, QCategory the category and QString the full SQL text. Categories are in OQCN with CategoryId and CatName. Search QString with LIKE to find every query that reads a table before an upgrade or a UDF change. Treat both tables as read-only: create and change queries in Query Manager or through the DI API, not with SQL updates, which SAP does not support.

**Cta:** If your users want to ask SAP Business One questions in plain English instead of building parameter queries, Netray can run that assistant on your own server.

**Faq:**

#### How do I add a parameter to an SAP Business One query?

**Answer:** Put [%0] where the value goes and compare it to a real column, for example WHERE T0.DocDate >= [%0]. When the query runs, B1 shows a prompt labelled with the field name. Use [%1], [%2] for more prompts. The same number used twice is asked once.

#### Which table stores all saved queries in SAP B1?

**Answer:** OUQR. QName is the query name, QCategory the category and QString the SQL text. Categories are in OQCN (CategoryId, CatName). Join OUQR.QCategory = OQCN.CategoryId to list queries by folder.

#### How do I use a variable with a SQL view in a B1 query?

**Answer:** On SQL Server use the comment pattern: /* SELECT FROM [dbo].[OCRD] T0 */, DECLARE @Card, /* WHERE */, SET @Card = /* T0.CardCode */ '[%0]', then SELECT from the view with WHERE CardCode = @Card. On HANA compare [%0] directly with the view column.

#### Why does my parameter not show a choose-from-list?

**Answer:** B1 only offers a list when it can tie the parameter to a known column, such as CardCode or ItemCode in a table in the FROM clause. If the parameter is in a variable without the comment pattern, or compared to an expression, you get a plain text prompt.

## Search terms

sap business one query parameters, sap business one query generator with parameters, sap b1 table that stores all queries, sap b1 query category table, sap business one variable with sql view, sap business one query manager table, sap b1 query [%0]

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