Step by step to use a stored procedure in a read.
The workaround is quite simple and requires the use of a SQL Server table variable, which defines the result set and allows SPD to discover the table metadata. Rewrite the stored procedure by declaring a table variable, insert the result of the dynamic SQL statement into the variable, and finally return the result set by reading the table variable. The changes to the sproc is shown in blue in this example:
Also, on the create procedure, we are returning the new auto generated ID using the following logic: SELECT CAST(SCOPE_IDENTITY() AS INT) AS [ReturnId]
The four auditing columns must be changed so that they are NOT required and so that they ARE read only. You will see warnings when setting these fields to read-only and these warnings may be ignored in this case. It’s important that we set the audit columns to read-only because we’ll eventually be using the two stored procedures created earlier to make our Create and Update operations. Neither of these stored procedures contains all four of the audit fields as input parameters. BCS operations are interdependent and will display errors when the Create and Update operations are later created if these fields are not set to read-only here. Next, the Read List operation may be created in the same way by setting the return parameter audit properties to read-only, the same way we’ve done the Read Item operation.
First, create a filter on your external content type’s Read List operation. Keep in mind that creating filters on a routine/stored procedure is a little different from creating a filter on a table or view. Instead of getting a separate Filters screen, you have to define a filter on an Input Parameter.
Set up BCS schema and stored procedures.
Import BCs schema.
Use Infopath Forms for Add/Edit/Delete forms.
Save as Template, import and then build out feature for External List.
BCS Model basics for Visual Studio project.
BCS XML basics.
BCS XML Snippets.
Create the bdc schema.
BDC xsd for the schema.