Skip to content

05 MSSQL Stored procedure as endpoint

Resources

  1. Auto generator for stored procedure parameters template-for-sql-parameters can be used to generate parameters for stored procedure endpoint.

A command whose body is a single name without spaces, for example [dbo].[task_set], is called as a stored procedure:

  • request parameters are matched to the procedure parameters by name (case-insensitive)
  • parameters with a null value are not sent, so the procedure defaults apply
  • the procedure runs in a transaction; see transactions

Example 1 - auto parameters mode

When the SRS defines no parameters, or only server-side parameters, all request parameters that match stored procedure parameter names are sent.

XML
1
2
3
4
5
6
7
8
<srs label="Execute sql ">
  <def> 
    <itm model="param" name="task_modified_by" opts="server">[[kv.v.user.username]]</itm>
    <itm name="api">
        [dbo].[task_set]
    </itm>
  </def>
</srs>

Example 2 - with defined parameters

When the SRS defines client parameters:

  • only the defined parameters are sent
  • you can pass server-side values, for example <itm model="param" name="task_modified_by" opts="server">[[kv.v.user.username]]</itm>
XML
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
<srs label="API task POST">
  <def>
    <itm model="param" name="team_id"></itm>
    <itm model="param" name="type_id"></itm>
    <itm model="param" name="task_name"></itm>
    <itm model="param" name="task_description"></itm>
    <itm model="param" name="task_internal_notes"></itm>
    <itm model="param" name="task_reporter"></itm>
    <itm model="param" name="task_resolved_at"></itm>
    <itm model="param" name="task_tags"></itm>
    <itm model="param" name="task_modified_by" opts="server">[[kv.v.user.username]]</itm>
    <itm name="api">
        [dbo].[task_set]
    </itm>
  </def>
</srs>

Example 3 - procedure call with fixed arguments

A body with spaces is sent as SQL text, so you can pass arguments explicitly:

XML
1
2
3
4
5
6
7
8
<srs label="Dictionary">
  <def>
    <itm model="param" name="username" opts="server">[[kv.v.user.username]]</itm>


    <itm model="command" name="warehouse">[erp].[dictionary_bynameValue] @username =@username, @dictionary='warehouse'</itm>
  </def>
</srs>