Documentation

SQL Stored Procedure Job

The SQL Stored Procedure job type executes a stored procedure on a SQL database. Input parameters supplied in Polysync are bound to the procedure's parameters, and any OUTPUT parameters plus the procedure return code are read back into the run output. The procedure is identified by its schema-qualified name — Polysync stores that in the Job's External Id.

This job type is supported on the SQL Database platform.

Required job fields

  • External Id — the schema-qualified stored procedure name (e.g., dbo.usp_RefreshCustomers). Set automatically on import.
  • Job Type — SQL Stored Procedure (set automatically on import).

There are no static job attributes — the procedure's parameters are the Job's parameters and are discovered from the database.

Job discovery

Discovery reads sys.procedures for the procedure list and sys.parameters for each procedure's parameters. Each parameter becomes a Job parameter with a Data Type mapped from its SQL type (int/bigint/smallint/tinyint → Int, bit → Bool, everything else → String) and a Direction derived from its SQL mode (input → Input, OUTPUT → Input & Output).

Parameter handling

At execute time Polysync obtains the procedure's authoritative parameter signature from the server (SqlCommandBuilder.DeriveParameters), so every parameter is bound with the correct SQL type, size, and direction. Supplied values are converted to the declared type (numbers, booleans, dates, GUIDs, etc.).

Direction Sent to procedure Read back into output
Input ✅ ❌
Output ❌ (seeded NULL) ✅
Input&Output ✅ ✅

Rules:

  • Leave a parameter blank to use its SQL default. Input-only parameters with no value are omitted from the call so the procedure's = DEFAULT applies.
  • Output and input-output parameters are always included so their values can be read back; if you don't supply a value they start as NULL.
  • The procedure return code is always captured as a Return Value output.

Output parameters

When the procedure completes successfully, Polysync records:

  • the final value of every OUTPUT / INPUT-OUTPUT parameter, and
  • the procedure return code under the key Return Value.

These are stored as the task run's output parameters and can be mapped into downstream (child) tasks via task dependencies.

Reading OUTPUT values requires VIEW DEFINITION on the procedure. Without it, Polysync falls back to sending only the supplied values as untyped input parameters (input-only procedures still run, but outputs are not captured).

Execution flow

  1. Polysync opens a connection to the configured server/database using the platform's authentication method (an Entra token is assigned to the connection for the Entra methods; SQL Authentication travels in the connection string).
  2. It derives the procedure's parameters, binds the supplied input values, and executes the procedure with CommandType.StoredProcedure, honouring the platform's Command Timeout (Seconds).
  3. The run completes synchronously:
    • success → status Success, with output parameters + return code captured;
    • a SqlException → status Failed, with a clear SQL error message.
  4. Because the status is terminal on return, the dispatcher skips polling. The status is also encoded into the RunId ("{Status}|{guid}|{procName}") so a later status check is consistent without re-running the procedure.
  5. Cancel is not supported — the procedure has already completed by the time a cancel request could arrive.

Monitor URL

None. A SQL database has no per-execution "run" page, so no external monitor link is produced. Use SQL Server tooling (Query Store, sys.dm_exec_*, Extended Events, or Azure SQL auditing) to inspect historical executions.

Troubleshooting

Symptom Likely cause Fix
Could not find stored procedure (2812) Wrong External Id / schema Use the schema-qualified name (e.g., dbo.usp_Name)
Failed to convert a parameter value Supplied value doesn't match the SQL type Enter a value in the parameter's type format (e.g., ISO date 2026-07-30)
Output parameters empty Missing VIEW DEFINITION, or the parameter isn't declared OUTPUT Grant VIEW DEFINITION; confirm the procedure declares the parameter OUTPUT
Procedure uses defaults unexpectedly overwritten A blank value was sent for an input parameter Leave the parameter blank to keep the SQL default; only fill it to override
Timeout after a long run Command Timeout (Seconds) too low Increase it on the platform