I am replacing a plain select statement with a stored procedure call in a SQL Call builder as the logic is getting more complicated with new customer requirements. The procedure is working correctly but it looks like PF is not able to understand the resultset and create the resulting schema/variable.
Below is the procedure. Do I need to explicitly define the returning data with something like a ref cursor?
CREATE PROCEDURE dbo.SELECT_OPPORTUNITIES
-- Add the parameters for the stored procedure here
SELECT field1, field2, field3 FROM table1
DGawron 10000054HC251 Posts
Re: Stored procedure with SQL Server2010-04-06T21:56:54ZThis is the accepted answer. This is the accepted answer.SQL Call doesn't generate a result schema for stored procedure calls. But it does fetch the results and drop them into an XML variable that contains all of the result sets (if multiple are returned) and any output parameters. Look for a variable with "Shadow" in the name. This will contain the full set o results. SQL Call also copies the first result set into separate variable that you typically feed to View & Form or some other page display builders.
As for the schema. You'll need to generate that manually and asociate it with the variable or service operation so that consumer models see a plain old service op.