OutSystems platform can call existing stored procedures and functions from your existing databases using its SQL Tool

Although virtually anything one can do with a stored procedure can also be done with the Platform, in many scenarios, legacy data and logic needs to be kept instead of re-written or migrated. This is the perfect usage for the Platform capabilities to integrate with your existing databases.

When building brand new applications we do recommend using the Platform visual modeling language to code all business logic given its built-in monitoring and deployment capabilities.

OutSystems platform does not generate stored procedures nor functions as part of its data access layer nor it automates the deployment or management of existing stored procedures.

1. Database Connection

Set up and test the database connection in the environment management console (Service Center)

2. Security

Adjust your SQL server security to allow the connection user to query data and execute the stored procedures

Note: OutSystems only requires datareader and datawirter. There is no need to grant higher DB roles. See the OSRUNTIME user permissions

3. Stored Procedure returning one value or code

Use the following code in the SQL Tool (aka advanced query)

/* %LogicalDatabase%=GetLogicalDatabase({Customer}) */
EXEC @return_value = [dbo].[spINSERT_dbo_Customer]
    @FirstName,
    @LastName ,
    @PhoneNumber ,
    @eMailAddress,
    @Priority ,
    @CreateDate

SELECT @return_value 

Use the LogicalDatabase to hint the platform on what database connection to use. In this case we are setting to use the same database connection set up for the customer table

You must create a structure to hold your result set

4. Stored procedure returning a Result Set

/* %LogicalDatabase%=GetLogicalDatabase({Customer}) */
EXEC [dbo].[spSELECT_CustomerEmailByID]
    @CustomerID 

Use the Logical Database to hint the platform on what database connection to use. In this case we are saying use the same database connection set up for the customer table

You must create a structure to hold your result set

You must have a single result set for each stored procedure

Code for the Stored Procedures:
 

USE [EXT_DB]
GO
/****** Object:  StoredProcedure [dbo].[spINSERT_dbo_Customer]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spINSERT_dbo_Customer] @FirstName varchar(25), @LastName varchar(25), 
@PhoneNumber varchar(15), @EmailAddress varchar(25), @Priority int, @CreateDate datetime

AS

SET NOCOUNT ON


-- 1 - Declare variables
DECLARE @CUSTOMERID int
-- 2 - Initialize variables

-- 3 - Execute INSERT command
INSERT INTO [dbo].[Customer]
           ([FirstName]
           ,[LastName]
           ,[PhoneNumber]
           ,[EmailAddress]
           ,[Priority]
           ,[CreateDate])
     VALUES
           (@FirstName
           ,@LastName
           ,@PhoneNumber
           ,@EmailAddress
           ,@Priority
           ,@CreateDate)
           
SELECT @CUSTOMERID= @@IDENTITY

return @CUSTOMERID

USE [EXT_DB]
GO
/****** Object:  StoredProcedure [dbo].[spSELECT_CustomerEmailByID]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spSELECT_CustomerEmailByID] 
    -- Add the parameters for the stored procedure here
    @CustomerID int
    
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT CustomerID,EmailAddress 
        from dbo.Customer
        where customerid = @CustomerID or @CustomerID = 0
    Order by CustomerID desc
END

• Created a database connection with a user that can execute the stored procedure
• Created an extension which maps at least 1 table/view from that database connection
• Remember to use the /* %LogicalDatabase%=GetLogicalDatabase({table/view}) */ in the advanced query so it knows which database to query
• You may need to fiddle a little bit with the procedure name EXEC [dbo].[spSELECT_CustomerEmailByID] note the square brakets, the dbo, etc. 

/* %LogicalDatabase%=GetLogicalDatabase({UNIT}) */
EXEC [dbo].[getItemCodeName]

/* %LogicalDatabase%=GetLogicalDatabase({UNIT}) */
--Select {UNIT}.*
--from {UNIT}
EXEC [AxDB30SpEU].[dbo].[getItemCodeName]