0

I create a Sql Type...

CREATE TYPE dbo.ScoreType AS TABLE ( ScoreID int, etc.... )

pass datatable from C# code into the stored procedure using the above defined type

ALTER proc [dbo].[InsertIntoScore] 
( @DateReg datetime, @stdLastName nvarchar(50), @stdFirstName nvarchar(50), 
@Description nvarchar(500),  @tvpScore ScoreType) 
AS 
.....
INSERT INTO dbo.Score (ScoreID, ....)     
SELECT dt.ScoreID, dt..... FROM @tvpScore AS dt;

But I can not use the ScoreType in @tvpScore ScoreType. please help me...

Error:

The table-valued parameter "@tvpScore " must be declared with the READONLY option.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Maryam Taghavi
  • 37
  • 1
  • 6
  • When you say you "can not" do something, be explicit and tell us what that means. E.g. if you are getting an error message, post an error message, it makes it much easier for others to not have to guess what might be going wrong. – Aaron Bertrand Jul 11 '12 at 18:02
  • The table-valued parameter "@'tvpScore " must be declared with the READONLY option. – Maryam Taghavi Jul 11 '12 at 18:14
  • I'm not sure you'll be able to use TVPs with EF. See http://stackoverflow.com/questions/8157345/entity-framework-stored-procedure-table-value-parameter and http://stackoverflow.com/questions/2837350/table-valued-parameter-in-stored-procedure-and-the-entity-framework-4-0 for explanations and possible workarounds. – Aaron Bertrand Jul 11 '12 at 18:36

1 Answers1

1

You need to say:

@tvpScore dbo.ScoreType READONLY
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • error in Entity Framework: The function 'InsertIntoScore' has a parameter 'tvpScore ' at parameter index 9 that has a data type 'table type' which is not supported. The function was excluded. – Maryam Taghavi Jul 11 '12 at 18:12
  • Maybe these can help (but you first have to add `READONLY` to the parameter declaration in the stored procedure, which is the cause the error message about the TVP not being declared with the `READONLY` option): http://www.deliveron.com/blog/post/Table-Valued-Parameters-with-Entity-Framework.aspx http://stackoverflow.com/questions/8157345/entity-framework-stored-procedure-table-value-parameter http://stackoverflow.com/questions/2837350/table-valued-parameter-in-stored-procedure-and-the-entity-framework-4-0 – Aaron Bertrand Jul 11 '12 at 18:35
  • @MaryamTaghavi: EF doesn't support TVP = you cannot map stored procedure with TVP in EF. – Ladislav Mrnka Jul 11 '12 at 20:04