Using External Content Types with Stored Procedures with Input Parameters

You must have tried External Content Types - the exciting new feature in SharePoint 2010 to connect to an external SQL table and provide a list where it lists, creates, updates data straight into the SQL table.

People always wonder, how do you get a ECT to work with a Stored Procedure, one that has input parameters.

You could do it this way...

Step 1: In your SharePoint Designer 2010, create a new external content type
Step 2: Connect to your SQL Server and then make sure you see the Routines (your stored procedures must be listed here)
Step 3: Rt Click on the Stored Procedure name and create operations for "Read Item" and "Read List" - both are required for the External Content Type's SharePoint list to be created.
During both these wizards, in the Input Parameters, your SP's Input Parameter will be listed. Here, you need to create a filter, which sends out it's value to the SP's filter. For example, if your SP's Input Parameter is @EmpID, and your SP is having a Select * from Employees where ID is present, you need to create a filter for ID, which maps to @EmpID.
Step 4. Create a WebPart Page and add the "Business Data Connectivity List" and in the Filters, you will see your Filter... there you specify the value for the Filter and it will be sent right back to your Input Parameter and bingo, you will find the data for the list being bound.

Screenshots below:


  1. I have a table "spstrial" which consists of 2 parameters "Name"(varchar 20) and "gender"(varchar 6). I have made a stored procedure names "spspstrial"

    create procedure spspstrial
    @name varchar(20),@gender varchar(6)
    insert into spstrial values(@name,@gender)
    select * from spstrial

    then i compile it and it compiled successfully. then i execute the stored procedure as exec spspstrial 'sidhanta','male'.Finally it executed sucessfully. Then in ect i create all type of operation for that stored procedure.When i create external list from that external stored procedure it gives error "The query against the database caused an error".From event viewer i come to know that " Cannot insert the value NULL into column 'Name', table
    'SidhantaDB.dbo.spstrial'; column does not allow nulls. INSERT fails." . Can anyone please share their ideas with me.

    With Thanks & Regards
    Sidhanta Tripathy

  2. Hey - thanks for this post - it saved me a bunch of time! You don't happen to have any tricks to pass those filter parms from another WebPart - would you?

  3. Hi,
    The Business Data Connectivity List is not available in category section. Please advise me how could i get that option.


  4. It's so nice article thank you for sharing a valuable content. Power BI Online Training

  5. Thank you for sharing the article. The data that you provided in the blog is informative and effective.

    Tableau Training in Hyderabad


  6. I am really happy to say it’s an interesting post to read . I learn new information from your article , you are doing a great job . Keep it up

    Devops Training in Hyderabad

    Hadoop Training in Hyderabad

    Python Training in Hyderabad

    Tableau Training in Hyderabad

    Selenium Training in Hyderabad


Post a Comment

Popular posts from this blog

The unattend answer file contains an invalid product key. Either remove the invalid key or provide a valid product key in the unattend answer file to proceed with windows installation

Multi Factor Authentication, Conditional Access for Power BI