Discoverable Procedures

Discoverable procedures in the MSSQL (BDK) Book.

Table Discovery

You must first discover a table to generate CRUD automation procedures for it:

discover "table/TableName" from mssql

1. List: Retrieve multiple rows with optional filtering

2. Create: Insert a single row

3. Bulk Insert: Insert multiple rows from a table/array

4. Get: Retrieve a single row by primary key

5. Update: Update a single row by primary key

6. Bulk Update: Update multiple rows from a table/array

7. Delete: Delete a single row by primary key

How Table Names are Processed

When you discover a table, the book "transforms" the table name to generate user-friendly procedures.

1. Sanitization

Table names are converted to PascalCase. For example:

• user_properties → UserProperties

• dbo.sp_GetUsers → DboSpGetUsers

• table_name → TableName

• hasShip → HasShip

2. Singularization

For certain operations, the system creates singular forms of table names:

• Users → User

• UserProperties → UserProperty

• Cars → Car

This means when you discover table/user_properties, the generated procedures will reference it as "UserProperties" table, and individual items will be referred to as "UserProperty".

Basic CRUD Operations

1. Retrieve Items (List)

Get all items from a table:

retrieve items from the TableName table

Get items with filtering:

retrieve items from the TableName table whose "column_name" is "value"

Get items with limit:

retrieve items from the TableName table whose "column_name" is "value"
    the limit is 10

2. Create Single Item

First, create a JSON object with the data:

create a json
use the above as the new item
set the new item's "column1" to "value1"
set the new item's "column2" to "value2"

create the new item in the TableName table

3. Bulk Insert

Insert multiple rows from an external data source (like Excel):

insert the content into the TableName table

Note: content should be a table structure with multiple rows.

4. Get Single Item by Primary Key

get a row from the TableName table
    the primary_key_column is "primary_key_value"

5. Update Single Item

create a json
use the above as the updated item
set the updated item's "primary_key_column" to "primary_key_value"
set the updated item's "column_to_update" to "new_value"
update the updated item in TableName table

6. Bulk Update

Update multiple rows from an external data source:

update the content into the TableName table

Note: content should include the primary key columns for matching existing rows and should be a table structure.

7. Delete Single Item

delete a row from the TableName table
    the primary_key_column is "primary_key_value"

---

Advanced Filtering

The book supports various filtering operations:

retrieve items from the TableName table whose "status" is "ACTIVE"
retrieve items from the TableName table whose "age" is greater than 18
retrieve items from the TableName table whose "name" contains "John"

---

Stored Procedure Operations

A stored procedure is a pre-written set of SQL commands that's stored in the database server and can be executed as a single unit. Think of it as a function that lives in your database instead of your application code.

Discovery

Discover a stored procedure to make it available for execution:

discover "stored procedure/schema.procedure_name" from mssql

Or for procedures in the default schema:

discover "stored procedure/procedure_name" from mssql

Execution

Execute a discovered stored procedure:

execute the stored procedure ProcedureName
    the Parameter1 is "value1"
    the Parameter2 is "value2"

Notes:

• Parameter names are case-insensitive

• Empty string values are converted to NULL

• The procedure name in execution uses the sanitized English name generated during discovery