Problem:
Calling Microsoft SQL Server Stored Procedures from OpenESQL
Resolution:
The attached program illustrates various methods of invoking SQL Server stored procedures. The procedures are very simplistic but demonstrate the mechanism for creating and calling stored procedures with SQL Server.
Solution:
The stored procedure 'mfexecsptest' has two input parameters and one output parameter. The procedure adds the two input parameters together to produce the output value. It is invoked in the first and second examples through an embedded sql CALL statement.
Example 1 demonstrates invoking the procedure with positional values, that is, the physical order of the input data in the CALL is associated, in order, with the stored procedure parameters as defined in the Create Procedure statement. This is referred to as 'passing by position'.
Example 2 demonstrates invoking the stored procedure and passing the data by reference. The name of each stored procedure parameter is referenced and a value or host variable is associated with each one using the format @parameter=value. Using this method, the parameter values may be specified in any order.
Example 3 illustrates passing multiple sets of input parameters through arrays to the stored procedure mfexecsptest. The output results are likewise returned through an array.
Example 4 demonstrates using cursor processing to obtain multiple output rows. This example invokes stored procedure 'mfexecsptest2', which returns multiple rows of data. The call to the stored procedure mfexecsptest2 is defined in the Declare Cursor statement. The cursor is opened and then the output data is fetched from the result set one row at a time.
$set sql(dbman=odbc)
program-id. testsp.
working-storage section.
EXEC SQL INCLUDE SQLCA END-EXEC
01 mfsqlmessagetext pic x(255).
01 param1 pic s9(9) comp-5.
01 param2 pic s9(9) comp-5.
01 param3 pic s9(9) comp-5.
78 aSize value 5.
01 arrayparam1 pic s9(9) comp-5 occurs aSize.
01 arrayparam2 pic s9(9) comp-5 occurs aSize.
01 arrayparam3 pic s9(9) comp-5 occurs aSize.
01 arrayCount pic s9(9) comp-5.
01 I pic s9(9) comp-5.
01 stateParam pic XX.
01 pubid pic x(4).
01 pubname pic x(40).
01 pubcity pic x(20).
procedure division.
exec sql whenever sqlerror goto sql-error end-exec
EXEC SQL
connect to 'mssqldata' user 'sa'
END-EXEC
exec sql whenever sqlerror continue end-exec
EXEC SQL
drop procedure mfexecsptest
END-EXEC
exec sql whenever sqlerror goto sql-error end-exec
*******Creating Stored procedure mfexecsptest******************
EXEC SQL
create procedure mfexecsptest
(@param1 integer ,
@param2 integer ,
@param3 integer output) as
select @param3 = @param1 @param2
END-EXEC
move 2 to param1
move 2 to param2
move 0 to param3
*********Example 1 Invoking mfexecsptest with positional parameters***********
exec sql
call mfexecsptest( :param1, :param2, :param3 output)
end-exec
display 'Testing positional parameters'
display param1 ' ' param2 ' = ' param3
move 5 to param1
move -5 to param2
move 10 to param3
*********Example 2 Invoking mfexecsptest with named parameters****************
exec sql
call mfexecsptest
@param2 = :param2 input,
@param3 = :param3 out,
@param1 = :param1 in
end-exec
display 'Testing named parameters'
display param1 ' ' param2 ' = ' param3
*********Example 3 Invoking mfexecsptest using parameter arrays**********
perform varying i from 1 by 1 until i > aSize
move i to arrayParam1(i)
add i i giving arrayParam2(i)
move 99 to arrayParam3(i)
end-perform
exec sql
call mfexecsptest(
:arrayParam1, :arrayParam2, :arrayParam3 output)
end-exec
display 'Testing whole array call'
perform varying i from 1 by 1 until i > aSize
display arrayParam1(i) ' ' arrayParam2(i)
' = ' arrayParam3(i)
end-perform
*********Example 4 Invoking mfexecsptest2 using cursors**********
EXEC SQL
drop procedure mfexecsptest2
END-EXEC
EXEC SQL
create procedure mfexecsptest2
(@stateParam char(2) ) as
select pub_id, pub_name, city from publishers
where state = @stateParam
END-EXEC
exec sql
declare c1 cursor for
call mfexecsptest2(:stateParam)
end-exec
move 'NY' to stateParam
exec sql
open c1
end-exec
display 'Testing cursor with stored procedure'
perform until exit
exec sql
fetch c1 into :pubid, :pubname, :pubcity
end-exec
if sqlcode = 100
exec sql close c1 end-exec
exit perform
else
display pubid ' ' pubname ' ' pubcity
end-if
end-perform
EXEC SQL
DISCONNECT CURRENT
END-EXEC
stop run.
sql-error section.
display 'error ' mfsqlmessagetext
stop run.
sql-warn section.
display 'warning ' mfsqlmessagetext
stop run.
