Using Stored Procedures to Return a Result Set
Returning a result set is necessary in situations where an app has several objects running connect and search commands to return different parameters. For example, an app has two separate tables and each presents a different set of data based on the search command. A DBA should already know how to set up and use a stored procedure in his own database, the only thing that remains is to integrate it into PerfectApps.
The following example is based on a sample stored procedure for Microsoft’s AdventureWorks database.
USE [AdventureWorks] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Get_Managed_Employees] ( @ManagerID int, @Title varchar(50) = NULL out, @Name varchar(100)= NULL out ) AS SELECT @Title=he.Title,@Name = pc.FirstName +' '+ pc.LastName from HumanResources.Employee he inner join Person.Contact pc on he.ContactID = pc.ContactID where he.EmployeeID = @ManagerID;
select EmployeeID,LoginID from HumanResources.Employee where ManagerID = @ManagerID; return 0 GO |
The screenshot below shows the results of the stored procedure in MS-SQL.
1.In PerfectApps, navigate to the Connections Dashboard. Next, expand the Databases node from the tree menu and select the desired database connection. Click the Add Action button. 2.On the New Action page, assign a name to the action and select Stored Procedure as the Type. 3.In the Details area, enter the Procedure Name as it was named when it was created on the database server. For example, using the EXECUTE statement shown above the procedure name would be: dbo.Get_Managed_Employees. 4.Parameters will use the procedure parameters as specified by their SQL names. The example shows one input, or Send parameter (ManagerID), and three output, or Return parameters (Title, Name, LoginID). 5.The result set columns are specified as Return parameters and should follow this naming convention: RS[index].ColumnName •RS specifies that this is a result set. •[index] is the result set index. Note that there can be more than one result set returned by a stored procedure. •ColumnName is the name of the column inside the result set. 6.Click Test to verify the action was configured properly. On the Test Action screen, click Connect.  7.Now, open the app and add a Connect command to an event on the app or object properties.  8.To see how the Connect command functions on the example app, enter the ID and click Search.  9.The image below shows the result of running the stored procedure. 
|
See also: Directory Services (LDAP) Connections, Connection and Action Permissions
Return to: Creating PerfectApps Connections, Integration
