SQL Database Integration

Re: Reading a Stored Procedure Output parameter

brandtsmith — Fri, 10 Sep 2010 17:33:24 GMT

I noticed that if you use :

DECLARE @return_value int

EXEC @return_value = [dbo].[prInsertInvoice]

@pInsuredID = 2090,

@pDueDate = '05/01/2010'

SELECT 'Return Value' = @return_value

as a data source to insert data into sql - you will get 2 rows inserted here...not one...only thing I can see doing it is the SELECT statement....which you need to get the return status...bummer...when I take out the SELECT statement only one row is inserted-but no Return Value is generated

Re: Reading a Stored Procedure Output parameter

Jimmy — Mon, 30 Nov 2009 05:21:58 GMT

To the best of my knowledge, the only way to inspect the XML structure in this situation would be to follow the code in a debugger and then look at the InnerXml property of the secondary data source.

To get an XPathNavigator positioned at the root of any secondary data source, use the DataSources object:

XPathNavigator dataSource = DataSources["NameOfDataSource"].CreateNavigator();

Then you can use the SelectSingleNode() method on this object to access any value in this data source. In the above case, the XPath would be "/dfs:myFields/dfs:dataFields/d:row/@SquareOut" to get at the SquareOut node.

Reading a Stored Procedure Output parameter

dkaplan — Mon, 23 Nov 2009 14:55:00 GMT

I want to use the value of the Output Parameter of a stored procedure in an InfoPath data element.

An example of a stored procedure which produces output parameters is described in http://www.infopathdev.com/blogs/jimmy/archive/2009/08/01/executing-a-stored-procedure-with-output-parameters.aspx I have run the example and understand the example up to the point of RETREIVING the two output parameters retrieved by the SELECT @squareOutput AS SquareOut, @halfOutput AS HalfOut statement. Specifically I do not understand the following quoted from the article:

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>

I do not understand how to see the XML structure. More importantly, how do I load either a string or an XpathNavigator object with the HalfOut and SquareOut values?

Thank You.