Using Stored Procedures to Approximate Output Parameters

While the Connector for OLE DB does not support output parameters, stored procedures can be used to generate a result set approximating output parameter functionality. This can be an effective way of accessing database-generated information when inserting or updating records. However, the Connector for OLE DB can only retrieve one result set from a stored procedure and it is possible for many result sets to be generated.

The stored procedure writer must be careful to generate only the desired result set. Avoid writing any stored procedure that clearly generates more than one result set. For example, a stored procedure with two select statements, such as select1 in the following example, is not compatible with the Connector for OLE DB. Using the Union operator and storing data in local variables or temporary tables are two helpful techniques for merging multiple select statements into one.

create table oledb_test (descriptionID int, description varchar(200), updtQty int)
go

insert into oledb_test values (1,"My description", 0)
go

create procedure select1 
as
 select count(*) from oledb_test 
 select distinct @newDescription from oledb_test
go

Insert, update, and delete statements within a stored procedure return the number of rows that were affected by the statement. OLE DB treats this affected row count as a result set. Thus, in the update1 stored procedure in the following example, two result sets would be generated. Fortunately, the affected row count reporting can be disabled with the Transact-SQL command set nocount on. Thus the update2 stored procedure in the following example, which uses the set nocount command, causes only the result set generated by the select statement to be returned.

create procedure update1 (@descriptionID int, @newDescription varchar(200))
as
 update oledb_test
 set description = @newDescription,
     updtQty = updtQty + 1
 where descriptionID = @descriptionID

 select updtQty from oledb_test where descriptionID = @descriptionID
go

create procedure update2 (@descriptionID int, @newDescription varchar(200))
as
 set nocount on

 update oledb_test
 set description = @newDescription,
     updtQty = updtQty + 1
 where descriptionID = @descriptionID

 set nocount off

 select updtQty from oledb_test where descriptionID = @descriptionID
go