• 1 reply
  • Latest Post - ‏2010-04-06T21:56:54Z by DGawron
9 Posts

Pinned topic Stored procedure with SQL Server

‏2010-04-02T14:37:34Z |
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?

-- Add the parameters for the stored procedure here
@parm1 nvarchar(50)
,@parm2 nvarchar(50)

SELECT field1, field2, field3 FROM table1

Updated on 2010-04-06T21:56:54Z at 2010-04-06T21:56:54Z by DGawron
  • DGawron
    251 Posts

    Re: Stored procedure with SQL Server

    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.