Add a New Database Lookup Using Stored Procedure

Add a Database Lookup using stored procedure to use the result set in the lookup.

• Follow the steps for Add a New Database Lookup till you a get the New Database Lookup dialog box.

To add a Database Lookup using a Stored Procedure

1. In the Database Connection list, click the name of the database connection.

2. Click Configure for the Procedure Details. The Procedure Details dialog box appears.

3. In the Procedure Name box, type the name of the stored procedure, and then click Load Details.

4. If there are parameters for the stored procedure, then the parameters appear. In such cases, do the following:

   1. By default, all parameters are selected. Click to clear parameters that are not required to be passed when the stored procedure is called, if required.

   2. Type values for the parameters, and then click Load Columns. The Value Column and the Display Name Column are populated.

5. In the Value Column list, click the column names to be used as the value fields.

6. In the Display Name Column list, click a column name to be used as the display field.

7. Click Save. The procedure details configuration is saved and the Procedure Details dialog box is closed.

8. Do one of the following:

   Task	Action
   Show the value field in the grid.	Click Yes for the Show Value Column.
   Show the display field in the grid.	Click No for the Show Value Column.

9. Click Save. The database lookup is created and the New Database Lookup dialog box is closed.

Note:
- The Title of database lookups cannot have special characters and blank space. The only allowed special character is _ (underscore). Also, the Title starting with _SYS_ is not allowed.
- Only the user defined stored procedures that return only the result set are supported.
- Only the first result set is supported for stored procedures that return multiple result sets.
- Only the columns from the result set, not the out parameters, are supported for the value and display name columns.
- Multiple value columns are currently not supported.
- The supported data types for the value column include bit, datetime, decimal, double, guid, int32, int64, nvarchar, single, uint16, uint32, uint64, and varchar.
- The supported data types for the parameters include bigint, bit, char, date, datetime, datetimeoffset, decimal, float, int, money, nchar, nvarchar(max), numeric, nvarchar, real, rowversion, smalldatetime, smallint, smallmoney, varchar(max),
time, tinyint, uniqueidentifier, and varchar.
- Only the mandatory parameters, that is the parameters that are selected, are made available in Data Lookup and Data Grid controls.
- The column specified in the Value field should identify a unique row in the table.
- While passing the parameters for lookups and data grid controls, ensure that the value for the Boolean data type is either True or False.
- While passing the parameters for lookups and data grid controls, ensure that the value for the Date Time control is converted from the UTC format to the local time format. For more information about the Date Time control, see Date Time.

Adding parameters for stored procedures

For string parameters, empty string value is passed, and not null values.

To check whether the string parameter value passed to the stored procedure is empty, add the following condition in the Where clause:

NULLIF(@EmpName,'') is null

Where, @EmpName is a string parameter in the stored procedure and NULLIF is an SQL function.

You can also check whether the string parameter is empty by adding the following condition in the Where clause:

@EmpName=‘’

Also while defining the parameters, ensure that the parameters are given default values as shown below:

CREATE PROCEDURE [dbo].EmpDetailsProc

-- Add the parameters for the stored procedure here

@EmpID decimal(18, 0) = null,

@EmpName nvarchar(MAX) = null,

@IsMarried bit = null,

@EmpDOJ datetime = null,

@Guid uniqueidentifier = null,

@Salary float = null

:

: