Home Dashboard Directory Help
Search

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


Status: 

Active


3
1
Sign in
to vote
Type: Bug
ID: 804010
Opened: 9/30/2013 7:51:16 PM
Access Restriction: Public
1
Workaround(s)
view
1
User(s) can reproduce this bug

Description

Hi,
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.
Details
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;
Sign in to post a workaround.
Posted by Steve Hood on 12/19/2013 at 1:44 PM
In SQL Server, you can do the workaround as already posted in the comments: exec sp_executesql N'usp_Save_Objs @objs = @objs',N'@objs [OBJ_TBL] READONLY',@objs=@p3;

In .NET, you can set the command to be include the parameter names on the first line. It's poor coding, but proper coding doesn't work.
            using (SqlCommand cmd = new SqlCommand("dbo.procTest @TableVar", connCentral))
            {
                SqlParameter parm = cmd.Parameters.AddWithValue("@TableVar", dtBlocking);
                parm.SqlDbType = SqlDbType.Structured;
                parm.TypeName = "dbo.TestType";