Call SQL Stored Procedure Action

The Call SQL Stored Procedure Action allows the designer to call a SQL Server Stored Procedure upon form Submission. The input parameters can be calculated, hardwired or mapped to responses to the form.

For this tutorial, we will create an Employee table and a stored procedure that will push the data into this Employee Table.

1. Creating a Table and a Stored Procedure

Following is the code for creating the Employee table and the Stored Procedure which will be used in this action.

CREATE TABLE [dbo].[Employee] (
  [EmployeeNo] [int] IDENTITY (1, 1) NOT NULL,
  [FirstName] [nvarchar](100) NULL,
  [LastName] [nvarchar](100) NULL,
  [Title] [nvarchar](100) NULL,
  [Salary] [numeric](7, 2) NULL,
  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
  (
  [EmployeeNo] ASC
  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE PROCEDURE [dbo].[SP_CreateEmployee]
--Input params

@FirstName nvarchar(100),
@LastName nvarchar(100) = NULL,
@Title nvarchar(100) = NULL,
@Salary numeric(7, 2) = NULL

AS

  BEGIN

    --Next Line is necessary - Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.
    SET NOCOUNT ON

    --insert the new record

    INSERT INTO Employee (FirstName, LastName, Title, Salary)
      VALUES (@FirstName, @LastName, @Title, @Salary)

    --return the entire record created by the stored proc

    SELECT @@IDENTITY AS NewEmployeeNumber

  END
GO

2. Configuring the Action in Design

This connector has the following attributes which need to be configured.

👍

Best Practice

Connection String should be configured in the Manage--> Settings--> Connector Settings and not in the action, unless you want to override the default connection string configured in Manage.

We are passing the following attributes in the action configured in above Design screenshot.

A record with EmployeeNo: 5 is created in the database when this action was executed.

In our stored procedure, we are returning the EmployeeNo as the output value, this value is available in the output Response of this action.

In this execution, the EmployeeNo as 5 will be returned from the Stored Procedure which would be available from the action output Response.

Receiving and Using a Response from the Save to SQL Server Action

Often the response from a stored procedure call via the Call SQL Stored Procedure Action is required for further calls. Most commonly the response is some sort of identifier created by the stored procedure for use in an on-screen message, email, SMS etc.

After a successful Call SQL Stored Procedure Action is performed, a response is returned to SmartIQ that can be used by pending actions. Specifically, this response is the first column of the first row in the result set returned by the query, additional columns and rows are ignored (also known as the executeScalar response).

For example, in the following stored procedure, the ID of the new record will be returned to SmartIQ

CREATE PROCEDURE spAddPerson

      -- Add the parameters for the stored procedure here

      @firstName nvarchar(255),

      @surname nvarchar(255)    

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;



    INSERT INTO Person (firstName,surname)

    VALUES (@firstName,@surname);

    SELECT @@IDENTITY AS 'Id';

END

GO

The value can then be used by a reference with the following syntax:

[ActionName.Response]

e.g. [Call SQL Stored Procedure.Response]

e.g. [AddPerson.Response]

In the example below, the response will be displayed in a download page message.

📘

Note

This is a good method of testing your Call SQL Stored Procedure Actions.