Skip to main content

SQL Procedure

The following chapter provides information about how a SQL Procedure can be connected.

Create a SQL Procedure in the Database​

Before connecting to a SQL Procedure via VDP, we guide you through how you can create one. A stored procedure is a prepared SQL code that you can save, such that the code can be reused over and over again. For more details on "CREATE PROCEDURE" (Transact-SQL) please refer to the Microsoft documentation. For our example we have defined the procedure as follows:

CREATE PROCEDURE [dbo].[fruitsproc]
    @PriceMax as numeric(10,5)
AS
    SELECT * FROM dbo.fruits
    WHERE price < @PriceMax

The "dbo" is the schema, "fruitsproc" is the name of the procedure, "PriceMax" is a parameter and "price" is a column of the table "fruits". SQL parameters start with an "@" character and should have descriptive names. VDP supports the following data types:

  1. VDP type Integer -> int, bigint, smallint, tinyint
  2. VDP type Decimal -> money, numeric, decimal, float
  3. VDP type String -> char, text, nchar, varchar, nvarchar
  4. VDP type Date & Time -> date, datetime, datetime2, datetimeoffset, smalldatetime, time
  5. NEW VDP type Date -> date
  6. NEW VDP type time -> time

In this example the parameter "PriceMax" works like a filter. Only rows which have a lower value than the entered parameter value are returned. In addition, make sure that any user who should be able to call that function is authorized for the execution.

Create a Function to connect to a Procedure​

Now let's create a new Function under a Category "CAT1", which has to be created before being able to connect to the above defined procedure. As usual, the information for "Function data", "Authentication", "Technical Parameters" and "User Parameters" have to be provided.

Function data​

The Function Data information like the Short Name and the System have to be entered in the standard window as usual:

Create Function for ODBC_PROC

The Technical Handler which is able to retrieve data from a relational database defined within a SQL Procedure is ODBC_PROCEDURE - vx.x.x (latest). Please find the general details in the Portal's create a new Function section, also for how to create a Function.

Authentication Configuration​

In case the "Shared Basic Auth (User and Password)" is configured by the Admin at System level, nothing has to be specified. Otherwise, enter the "User" and a "Password" which should be specifically used for this Function.

Technical parameters​

The following technical parameters have to be provided to connect to a table or a view:

  • PROCEDURE: The name of the procedure.

  • SCHEMA: "dbo", the name of the schema the table or view is located in.

User parameters​

The definition of the user parameters usually is the final step. In this step, the user parameters are defined which allow the dynamic parameterization of the Function directly from the applications. To create a new Parameter click on "New Parameter" in the "User Parameters" window.

Configure new End-User Parameters for ODBC_PROC

A new window pops up, where you can define a new Parameter. In the fruits example the root parameter type is "PARAMETER", Cardinality is single value, the atomic data type is "decimal, and we check the "Required" and "Exposed to end user" boxes. This defines the parameter as required and allows the end user to change it if needed. As Short Name you have to enter the exact same name you used in the definition of the procedure. In the fruits example case this is "PriceMax" (without the "@" character).

Configure End-User Parameters for ODBC_PROC

After entering all of the information, click on "Create" in order to add the new parameter. Afterwards, you can set a default value for the parameter. Save again and return to the Function configuration. In case the procedure can handle various other Parameters, create them according to the type specifications defined above.

Test the Function​

Now we should be able to test the procedure. Open the "Test Bench" via the button "Try in Test Bench" and execute the query with the default parameter. After a second the data should appear below in a table.

Test Results ODBC_PROC

In case the "PriceMax" Parameter is set to three, only rows with price values below three should appear. This shows that the procedure is configured correctly.