There comes a time when the Standard DB dips and query just isn’t enough and one requires to pass parameters to a Stored Procedure and get parameters as output.
Although the basic scenario of using a stored procedure is covered well in the Genesys Support Website but I my thoughts were getting multiple return parameters would be useful to know.
For this Post I will use the Scenario: a stored procedure that takes Email Address, NRIC and Phone Number as input in order to identify the customer and return the first and the last name, which can then be attached to User Data to be displayed on Workspace’s Case Data.
I will be using Genesys Framework 8.x and Microsoft SQL Server 2012 for this scenario.
Let’s start with creating a table and a stored procedure in SQL Server
SQL Server
Script for Table
CREATE TABLE [dbo].[newCustomer](
[custID] [int] IDENTITY(1,1) NOT NULL,
[Salutation] [nvarchar](20) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[NRICorPassport] [nvarchar](20) NULL,
[Address] [nvarchar](max) NULL,
[DataofBirth] [nvarchar](20) NULL,
[Telephone] [nvarchar](20) NULL,
[Email] [nvarchar](80) NULL,
[SecurityQuestion] [nvarchar](max) NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Script for Stored Procedure
CREATE PROCEDURE dbo.sp_getFullName @NRIC nvarchar(30) = NULL, @PNo nvarchar(30) = NULL, @Email nvarchar(150) = NULL, @FName nvarchar(150) Output, @LName nvarchar(150) Output
AS
SELECT @FName = FirstName, @LName = LastName
FROM newCustomer
WHERE NRICorPassport = @NRIC OR Telephone = @PNo or Email = @Email
GO
Interaction Routing Designer (IRD)
Now the SQL part is completed, Let’s move to the Genesys IRD portion
Before we start the Genesys portion, Please ensure you have a valid DAP that points to the your database and the connection of the DAP in the Connections TAB of IRD Application and URS.
Input and Output Variables
Starting the Database Wizard, Select the DB Access Point
and in the “data access expression” Select
Procedure
Enter the Name of the Stored Procedure and all input and output parameters
You can choose to Assign the value to a Variable or Attach the output as User Data to the call.
We will be Assigning the output to Variables.
Select “Assign each value to a variable when the output is a string of values ….”
;[
Last but not the least step of the Database Wizard is the specify the “Separator” (since out output is being return as a string and the Variables to assign the values to.
Once we have the value in Variables, we can attach those value to User Data and later use.
Output on Workspace
As seen below the two output parameters we received from the stored procedure
Log Analysis / Points of Interest
1- “Here is XDATA” This is the start of Database Block
2- The Actual Details being passed to SQL Server can be seen i.e. the name of stored procedure and all of the input and output parameters.
3- Output Parameters returned by the SQL Server
4- Assignment of the variables
