Can't join to tvp inside stored procedure

Question

When I'm calliing the SP(with JOIN any table [dbo].[pr_2]) from c#, I'm getting nothing(my DataReader contains 0 rows). But if I execute same SP in MSSMS I'm getting what i want.

I can't to join table to tvp parameter.

My TVP

CREATE TYPE [dbo].[GuidList] AS TABLE(
    [Id] [uniqueidentifier] NOT NULL
)

Stored procedure [dbo].[pr_1] This's SP work fine. I'm getting result in MSSMS and C#.

CREATE PROCEDURE [dbo].[pr_1]
  @tvp [dbo].[GuidList] READONLY
AS

BEGIN
    SELECT * 
    FROM @tvp
END

But if try to join any data(or table) I'm getting nothing in C#, but in MSSMS I'm getting what i want

Stored procedure [dbo].[pr_2]

CREATE PROCEDURE [dbo].[pr_2]
  @tvp [dbo].[GuidList] READONLY
AS

BEGIN
    SELECT 
        [id]
        ,[Name]
        ,[Email]
    FROM [dbo].[User] users
        inner join @tvp tvp on tvp.Id = users.UserId
    WHERE users.isActive = 1
END

And my c# code

DataTable tvp = new DataTable();
tvp.TableName = "[dbo].[GuidList]";
tvp.Columns.Add("Id", typeof(Guid));

for (int i = 0; i < 10; i++)
{
    DataRow row = tvp.NewRow();
    //just for example
    row["Id"] = Guid.NewGuid();
    tvp.Rows.Add(row);
}

DataTable result = new DataTable();

using (SqlConnection connection = new SqlConnection("MyConStr"))
{
    using (SqlCommand command = new SqlCommand())
    {
        try
        {
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "[dbo].[pr_2]";

            SqlParameter tvpSqlParam = new SqlParameter("@tvp", tvp);
            tvpSqlParam.SqlDbType = SqlDbType.Structured;
            tvpSqlParam.TypeName = "[dbo].[GuidList]";

            command.Parameters.Add(tvpSqlParam);

            connection.Open();

            using (SqlDataReader reader = command.ExecuteReader())
            {
                result.Load(reader);
            }

        }
        catch (SqlException sqlException)
        {
        }
        finally
        {
            command.Parameters.Clear();
        }
    }
}

I don't know what is that. Why my SqlCommand returns nothing but

declare @TVP [dbo].[GuidList]
insert into @TVP
SELECT [ContractTemplateId]
FROM [dbo].[MyTable]

exec [dbo].[pr_2] @tvp = @TVP

return waht i want?

This's my connection string

Data source=srv;Initial catalog=catcalog;Persist security info=True;User id=test;Password=pass;Connect Timeout=30;Max Pool Size=100;Connect Timeout=60;Failover Partner=SRV;

I am not sure, I used the same way you did and it was working. Try change this line- tvp.TableName = "[dbo].[GuidList]"; to tvp.TableName = "GuidList"; — M_Idrees, Sep 07 '15 at 14:07
@MuhammadIdrees but my [dbo].[pr_1] is working with tvp.TableName = "[dbo].[GuidList]"; — isxaker, Sep 07 '15 at 14:13
Have you tried this? Because this is the only difference I noted with my working code. rest of the things are same. — M_Idrees, Sep 07 '15 at 14:23
you don't need to specify type name on the parameter nor do you need to specify table name on the data table. I'm guessing the problem is that you don't pass the same data when you run your procedure from code as you do when you run it from ssms. There is no problem whatsoever in using TVPs in joins in sql server. — Zohar Peled, Sep 07 '15 at 14:24
Basic question: do you connect to the same database using c# and SSMS? — Lukasz Szozda, Sep 07 '15 at 14:25
i don't understand why you believe that your join should return something? not that i use the datatype `uniqueidentifier`but i'm pretty sure the return of `Guid.NewGuid();` is not an `UserId` — A ツ, Sep 07 '15 at 14:28
@Aツ: In fact, you may say that it's almost impossible by definition :-) — Zohar Peled, Sep 07 '15 at 15:13

Can't join to tvp inside stored procedure

0 Answers0