Can't Passing a Table-Valued Parameter to a Stored Procedure Through sp_executesql? - by 亂馬客

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 804010 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 9/30/2013 7:51:16 PM
Access Restriction Public


   I call stored procedure passing a Table-Valued Parameter through sp_executesql then the sp can't receive Table-Valued Parameter's value. 
If i call direct, the sp can receive Table-Valued Parameter's value.
Sign in to post a comment.
Posted by Steve Hood on 12/19/2013 at 1:48 PM
I was able to reproduce this on SQL 2008 Std x64, SQL 2008 Ent x64, and SQL 2012 Std x64. My original code to reproduce the issue came from code automatically generated by C# in Visual Studio 2013, which makes me wonder how this could still be an unresolved issue.

The buggy version:
using (SqlCommand cmd = new SqlCommand("dbo.procTest", connCentral))

The workaround:
using (SqlCommand cmd = new SqlCommand("dbo.procTest @TableVar", connCentral))
Posted by Microsoft on 11/1/2013 at 11:58 AM
Thank you for submitting this feedback. We are investigating the issue and will update you when we have more information.
Posted by Jon Seigel on 10/2/2013 at 1:21 PM
exec sp_executesql N'usp_Save_Objs @objs = @objs',N'@objs [OBJ_TBL] READONLY',@objs=@p3;