Monday, April 09, 2012

Table-Valued Parameters in Stored Procedure with Entity Framework

I was trying to execute one SQL server 2008 Stored Procedure that is having input parameter as table variable and couldn’t find any way to do this, finally I found that table valued parameter not supported by entity framework till 4.1 version and this is coming soon in next upcoming version. Whereas Table- Valued function supported on version 4.1. Please vote for Table-Valued function to be implemented with Entity Framework next version, below the link to vote for this.

Meanwhile you will have some workaround
  • Send common separated string value into Stored Procedure
            Ex: India, Japan, USA, Korea

Here you have to create Split function to call inside your Stored Procedure, see following link to create Split Function

  • Send XML String as an input parameter to Stored Procedure

string CountryNames = '<ArrayList>
<Values> <Value>India</Value> </Values>
<Values> <Value>Japan</Value> </Values>
<Values> <Value>USA</Value> </Values>

            You can use following sub stored procedure to read data from XML

Create procedure [dbo].[GetCountryNames]
    @xml VARCHAR(MAX)
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

            SELECT  Value
            FROM    OPENXML (@hDoc, '/ArrayList/Values',2)
            WITH    (Value VARCHAR(MAX))
    EXEC sp_xml_removedocument @hDoc

Table-Valued Function (TVF) is supported in Entity Framework 4.2