How to Work with SQL Stored Procedures in SmartConnect

Instead of pointing to a table or view, you can execute more advanced queries with complex execution and data cleansing.

Example:

CREATE PROCEDURE [dbo].[SmartConnectDataSourceProcedure]
AS
BEGIN
    SELECT 
        VendorID, 
        Vendor_Name, 
        ProductName, 
        Price
    FROM 
        dbo.BC_PO
    WHERE 
        VendorID = 10000;
END

1. Open SmartConnect
2. Navigate to Data Sources > Bulk Sources > Query
3. Create a MSSQL Query data source
4. Execute your stored procedure in the Query

SmartConnect can call a stored procedure as a destination instead of writing directly to a table. Input parameters can be mapped from SmartConnect, and output parameters will be sent back to SmartConnect with success and failure statuses.

Example:

CREATE PROCEDURE [dbo].[SmartConnectProcedure]
	@VendorId VARCHAR(50),
	@VendorName VARCHAR (50),
	@PO_Id VARCHAR (50),
	@Product VARCHAR (50),
	@ProductName VARCHAR (50),
	@Quantity INT,
	@Price Float,
	@ErrorMessage VARCHAR(255) OUTPUT,
	@Error Int OUTPUT
AS 
BEGIN
BEGIN TRY

    -- ==========================================================
    -- Replace the section below with your own custom logic
    -- This is just a sample that inserts into BC_PO if the vendor is not on hold
    -- ==========================================================

    IF NOT EXISTS (
        SELECT [VendorID] 
        FROM [MyVendors] 
        WHERE [VendorID] = @VendorId 
          AND [HoldStatus] = 1
    )
        INSERT INTO dbo.BC_PO (
            [PO_Id],
            [VendorID],
            [Vendor_Name],
            [ProductId],
            [ProductName],
            [Quantity],
            [Price]
        )
        VALUES (
            @PO_Id, 
            @VendorId, 
            @VendorName, 
            @Product, 
            @ProductName, 
            @Quantity, 
            @Price
        )
    ELSE 
        SET @Error = 11 
        SET @ErrorMessage = 'Vendor is on hold'

    -- ==========================================================
    -- End of custom logic section
    -- ==========================================================

END TRY
BEGIN CATCH
    SET @Error = ERROR_NUMBER() 
    SET @ErrorMessage = ERROR_MESSAGE()
END CATCH

IF @Error IS NULL
BEGIN
    SET @Error = 0 
END
END

1. Open SmartConnect > Integration Processes
2. Add New or Open Process
3. Navigate to the Target tab
4. Select Microsoft Sql Server
5. select Type Procedures
6. Select your procedure to import into, and map it on the integration tab

As a Pre-Map Task

Run a stored procedure before the map executes before pulling data from the data source.

As a Post-Document Task

Execute a stored procedure after the record is processed.

Input parameters accepted: Source Columns, Global Variables

As a Post-Map Task

Execute a stored procedure after the map completes.

Input parameters accepted: Global Variables