Calling SQL Server Stored Procedures

Wherever procedures are allowed, you can use a Microsoft™ SQL server stored procedure such as the destination of a Direct Transfer or within an LC LSX script. The stored procedure being called must have its parameters set up correctly for the call.

Input values are provided to SQL procedures as named parameters. This means that the parameters in SQL must use the same names as the fields being provided as input values. These inputs include key values when used in the context of a keyed operation (selection, update, or delete context), and data values when relevant (insert or delete context). The input value data types are selected as the closest match to the data type in IBM® Lotus Enterprise Integrator® (LEI) or Domino® Enterprise Connection Services (DECS), and are converted by SQL to the procedure parameter defined data types, as long as the conversion is supported by Microsoft™ SQL server.

Any output from a stored procedure must be returned through a final SELECT statement in the procedure. The result set of this selection will become the result set produced by the stored procedure.

The following is an example of a Microsoft™ SQL server stored procedure. This is the format that would be required for the Open event of a Advanced RealTime Virtual Fields Notes® activity assuming that the key field is called NUMBER1 and the data fields are called NUMBER2, TEXT1, and TEXT2. In this context, the key field is the input parameter, and the result set is expected to include the data fields followed by the key field.

create procedure OpenProc @NUMBER1 int 
as select NUMBER2, TEXT1, TEXT2, NUMBER1 from tablename 
where NUMBER1 = @NUMBER1

For example, in the following stored procedure, new data is being inserted into a table called emp_back.

CREATE PROCEDURE DECS_CREATE @FIRSTNAME varchar(100), @LASTNAME char(100), @DEPTNO int, @HIREDATE datetime, @BIRTHDATE smalldatetime, @GRADE real, @EMPNO int as insert into EMP_BACK (EMPNO, FIRSTNAME, LASTNAME, DEPTNO, HIREDATE, BIRTHDATE, GRADE) values (@EMPNO, @FIRSTNAME, @LASTNAME, @DEPTNO, @HIREDATE, @BIRTHDATE, @GRADE)

To reiterate, when you map fields in an activity document, mapping must match the portion of the stored procedure that directs where the new data is to be stored.

CREATE procedure QECreateaddrbook(
@CompanyName varchar(20),
@MailDomain varchar(20), 
@MailServer varchar(20),
@MailAddress varchar(20),
@MiddleInitial varchar(1),
@State varchar(20),
@FirstName varchar(20),
@LastName varchar(20))
as
INSERT INTO Venturi.addrbook (FirstName,MiddleInitial,LastName, MailDomain, MailServer, MailAddress, CompanyName,State)
VALUES
(@FirstName, @MiddleInitial, @LastName, @MailDomain, @MailServer, @MailAddress, @CompanyName, @State)

create procedure QEDeleteaddrbook (@FirstName varchar (20), @LastName varchar (20)) as DELETE FROM addrbook WHERE FirstName=@FirstName and LastName = @LastName

CREATE procedure dbo.QESelectaddrbook(@FirstName varchar(20),@LastName varchar(20)) as
select FirstName, MiddleInitial, LastName, MailDomain, MailServer, MailAddress, CompanyName, State from Venturi.addrbook 
WHERE  FirstName = @FirstName and LastName = @LastName

CREATE procedure dbo.QEUpdateaddrbook(
@CompanyName varchar(20), 
@MailDomain varchar(20), 
@MailServer varchar(20), 
@MiddleInitial varchar(1),
@State varchar(20),
@MailAddress varchar(20), 
@FirstName varchar(20), 
@LastName varchar(20))
as
UPDATE Venturi.addrbook SET
MailDomain=@MailDomain, MailServer=@MailServer, MailAddress=@MailAddress, CompanyName=@CompanyName, State=@State 
WHERE 
FirstName=@FirstName and LastName=@LastName