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:

Comments

  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)
    as
    begin
    insert into spstrial values(@name,@gender)
    select * from spstrial
    end

    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

    ReplyDelete
  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?

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

    Thanks.

    ReplyDelete

Post a Comment

Popular posts from this blog

Windows 7 Blue Screen because of ConnectiFy

HTTPS Site InfoPath Issue : You do not have permissions to access a web service that provides data required for this form to function correctly