Ritesh Kesharwani: April 2012

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.

http://data.uservoice.com/forums/72025-entity-framework-feature-suggestions/suggestions/1015347-table-valued-parameters

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

http://stackoverflow.com/questions/6861021/passing-an-array-table-to-stored-procedure-through-entity-framework

Send XML String as an input parameter to Stored Procedure

Ex:

string CountryNames = '<ArrayList>

<Values> <Value>India</Value> </Values>

<Values> <Value>Japan</Value> </Values>

</ArrayList>'

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

Create procedure [dbo].[GetCountryNames]

@xml VARCHAR(MAX)

BEGIN

SET NOCOUNT ON ;

DECLARE @hDoc INT

EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT Value

FROM OPENXML (@hDoc, '/ArrayList/Values',2)

WITH (Value VARCHAR(MAX))

EXEC sp_xml_removedocument @hDoc

END

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