Many people are aware that you can use InfoPath code or script to perform all sorts of database queries on tables, views, stored procedures and the like.  I won't go into the details here, but you can read more about this at the following links:

http://www.infopathdev.com/forums/p/9467/33496.aspx#33496
http://www.infopathdev.com/forums/p/8940/31780.aspx#31780

 But what happens when you want to query a stored procedure that uses OUTPUT parameters?  I ran into this issue while helping a forum poster in this thread:

http://www.infopathdev.com/forums/t/12506.aspx

 Suppose I have this elementary stored procedure, which returns the square and half of the input value:

CREATE PROCEDURE [dbo].[SimpleStoredProcedure]
     @inputValue int, 
     @inputSquared bigint output,
     @inputHalved int output
AS
BEGIN
     SET NOCOUNT ON;
     SET @inputSquared = CAST(@inputValue as bigint) * CAST(@inputValue AS bigint)
     SET @inputHalved = @inputValue / 2

     -- return some value just for the heck of it
     return 7
END

If I try to execute this procedure from InfoPath code like this (C# 2007 here):

AdoQueryConnection conn = DataConnections["TestDatabase"] as AdoQueryConnection;
string originalCommand = conn.Command;
string query = "EXECUTE SimpleStoredProcedure 23";  // just pass in 23 as an arbitrary value
conn.Command = query;
conn.Execute();
conn.Command = originalCommand;
RunQuery(query); 

I get this error:

The query cannot be run for the following DataObject: TestDatabase
InfoPath cannot run the specified query.
[0x80040E10][Microsoft OLE DB Provider for SQL Server] Procedure or function 'SimpleStoredProcedure' expects parameter '@inputSquared', which was not supplied.

this is because my query does not give the stored procedure any place to store the values of its output parameters.  What's more, I have no way of accessing these parameters because by default, InfoPath only receives the value of the return statement from a stored procedure, which would always be 7 in this case.

The solution:

The thing to remember here is that you don't have to limit yourself to having just one SQL statement in your query.  You can have several.  And that's just what we need here.

What we can do is have the query create some SQL variables to hold the output parameters, and then SELECT them so that their values get sent back to InfoPath.  First we create the SQL variables and pass them into the stored procedure:

DECLARE @squareOutput bigint, @halfOutput int
EXECUTE SimpleStoredProcedure 23, @squareOutput OUTPUT, @halfOutput OUTPUT

We can do this by modifying the third line of my code above to:

 string query = "DECLARE @squareOutput bigint, @halfOutput int " +
    "EXECUTE SimpleStoredProcedure 23, @squareOutput OUTPUT, @halfOutput OUTPUT";
[GOTCHA: Don't forget the space at the end of the first line of the statement or you will wind up with something like intEXECUTE in your statement]

This is still incomplete because no values come back from the query, and the secondary data source's contents look like this:

<dfs:myFields xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"/>

To finish this solution off, we need to SELECT the value of the SQL variables so that they will be sent back to InfoPath, like this:

SELECT @squareOutput AS SquareOut, @halfOutput AS HalfOut

I've specified aliases (SquareOut and HalfOut) for the SELECT column names because otherwise they will come back without names and InfoPath will give them meaningless names like c0 and c1.

The code looks like this at this point:

 string query = "DECLARE @squareOutput bigint, @halfOutput int " +
    "EXECUTE SimpleStoredProcedure 23, @squareOutput OUTPUT, @halfOutput OUTPUT " +
    "SELECT @squareOutput AS SquareOut, @halfOutput AS HalfOut";
[Again, don't forget the space at the end of the second line of the statement]

Finally, we run the query, and we get back our result, with our requested values:

<dfs:myFields xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution">
     <dfs:dataFields>
          <d:row xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields" HalfOut="529" SquareOut="11"/>
     </dfs:dataFields>
</dfs:myFields>