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
  4. 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 upEmbedded Systems Course in Hyderabad

    ReplyDelete
  5. "Looking to kickstart your career in SAP EWM? Bangalore offers a plethora of options for SAP EWM training.
    Here’s what to consider:
    SAP EWM Training in Bangalore at SAP Masters
    1.Quality Training:
    Opt for best institutes with a solid reputation and accreditation from SAP Masters Training institute.
    Look for expert faculty who can provide comprehensive insights.
    2.Curriculum:Ensure the curriculum covers core EWM concepts and offers hands-on projects for practical learning.
    3.Infrastructure: Check for modern facilities and labs equipped with the latest software to support your training needs.
    4.Placement Support: Choose institutes that offer robust placement assistance, including resume building and interview preparation.
    Consider institutes like SAP Masters Institute of Technology, sap masters Academy, and sapmasters training institute bangalore,
    known for their quality training and successful placements. Choose wisely, and jumpstart your SAP EWM journey in Bangalore!


    Visit SAP Masters - Best SAP Training in Bangalore"
    Visit SAP Masters - SAP EWM Training in Bangalore"

    ReplyDelete

Post a Comment

Popular posts from this blog

Multi Factor Authentication, Conditional Access for Power BI