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.
SQL Server connections are now secure by default, which means that encryption is expected to be enabled for these connections.
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.
|SQL Server Connection String||The connection string for the SQL Server.|
Connection String should look like:
Server=myserverlocation;Initial Catalog=MyCatalog;Persist Security Info=False;MultipleActiveResultSets=True; Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;
Only include this attribute if you want to override the Default Connection String that is stored within Manage > Settings > SQL Server.
|Stored Procedure Name||The name of the Stored Procedure which would be executed||Yes|
|Stored Procedure Parameter||The parameters which will be passed to the Stored Procedure during execution||Yes|
Connection String should be configured in Manage > Settings > SQL Server. 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.
|SQL Server Connection String||Data Source=IX10-VIPIN\SQLEXPRESS;Initial Catalog=Intelledox101;User ID=sa;Password=****|
|Stored Procedure Name||SP_CreateEmployee|
|Stored Procedure Parameter||FirstName|
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:
e.g. [Call SQL Stored Procedure.Response]
In the example below, the response will be displayed in a download page message.
This is a good method of testing your Call SQL Stored Procedure Actions.
Updated 3 months ago