locked
Table-valued parameter RRS feed

  • Question

  • i am passing table from C# to sql.
    on sql this is my code :
    CREATE TYPE [dbo].[CampaignIdList] AS TABLE(
     [BLMJ] NVARCHAR(50) NOT NULL
     )
    GO
    CREATE PROCEDURE [dbo].[casp_GetExist] 
    	@BLMJ_LIST CampaignIdList READONLY
    AS
    BEGIN
    	SET NOCOUNT ON;
     Select * from @BLMJ_LIST
    
    END
    GO
    

    then i take the code as i see it on the profiler :
    declare @p3 dbo.CampaignIdList
    insert into @p3 values(N'1247')
    insert into @p3 values(N'1261')
    insert into @p3 values(N'1277')
    insert into @p3 values(N'1279')
    insert into @p3 values(N'1289')
    select * from  @p3 -->> isee that there is data in @p3
     execute sp_executesql N'exec casp_GetExist',N'@BLMJ_LIST CampaignIdList READONLY',@p3
    

    when i run the code that i take from the profiler, and i expect for result
    from the query : Select * from @BLMJ_LIST
    i get no rows as result.
    any idea why?

    • Edited by Kalman Toth Wednesday, February 20, 2013 1:33 PM wrong name
    Wednesday, February 20, 2013 12:23 PM

Answers

  • solved :

    cmd = new SqlCommand("casp_GetExist", cnt);

    changed to :

    cmd = new SqlCommand("casp_GetExist @BLMJ_LIST=@BLMJ_LIST", cnt);

    • Marked as answer by pelegk1 Wednesday, February 20, 2013 1:18 PM
    Wednesday, February 20, 2013 1:16 PM

All replies

  • Why using sp_executesql?????

    Simple

    exec [casp_GetExist] @BLMJ_LIST=@p3


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Proposed as answer by Eswararao C Wednesday, February 20, 2013 12:31 PM
    Wednesday, February 20, 2013 12:27 PM
    Answerer
  • Try the below:

    declare @p3 dbo.CampaignIdList insert into @p3 values(N'1247') insert into @p3 values(N'1261') insert into @p3 values(N'1277') insert into @p3 values(N'1279') insert into @p3 values(N'1289') select * from @p3 -->> isee that there is data in @p3 execute sp_executesql N'exec casp_GetExist @BLMJ_LIST=@BLMJ_LIST',N'@BLMJ_LIST CampaignIdList READONLY',@p3



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, February 20, 2013 12:53 PM
  • when i run the code that i take from the profiler, and i expect for result
    from the query : Select * from @BLMJ_LIST
    i get no rows as result.
    any idea why?

    If you look closely at the sp_execute_sql statement in the trace, the TVP is not being passed to the stored procedure.  Consequently, the table is empty when the proc executes.  The statement in the Profiler trace should look something like:

    execute sp_executesql N'casp_GetExist @BLMJ_LIST',N'@BLMJ_LIST CampaignIdList READONLY',@p3

    I suspect the problem is with the C# code.  Make sure you specify CommandType.StoredProcedure and have only the stored procedure name in the CommandText (e.g. "dbo.casp_GetExist" instead of  "exec casp_GetExist".  If you still need help, please post your C# code.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Naomi N Wednesday, February 20, 2013 1:23 PM
    Wednesday, February 20, 2013 12:55 PM
  • hi all non of the above worked.

    Uri - i can't control the use of sp_executesql, beacuse i access the db from C#.

    did any one manage to run the code and get result? this will solve the problem.

    this is my C# code :

    cnt = Database.ConnectionSQL(Database.db.Billing);
            try
            {
                cmd = new SqlCommand("casp_GetExist", cnt);
                SqlParameter parameter = new SqlParameter();
                //The parameter for the SP must be of SqlDbType.Structured 
                parameter.ParameterName = "@BLMJ_LIST";
                parameter.TypeName = "CampaignIdList"; 
                parameter.SqlDbType = System.Data.SqlDbType.Structured;
                parameter.Value = dataTable;
                cmd.Parameters.Add(parameter); 
                ret = cmd.ExecuteScalar();
            }

    Wednesday, February 20, 2013 1:10 PM
  • Latheesh you code is the solution, the problem is what should i change on the C3 side?

    not working

    execute sp_executesql N'casp_GetExist',N'@BLMJ_LIST CampaignIdList READONLY',@p3

    working

    execute sp_executesql N'exec casp_GetExist @BLMJ_LIST=@BLMJ_LIST',N'@BLMJ_LIST CampaignIdList READONLY',@p3

    Wednesday, February 20, 2013 1:13 PM
  • solved :

    cmd = new SqlCommand("casp_GetExist", cnt);

    changed to :

    cmd = new SqlCommand("casp_GetExist @BLMJ_LIST=@BLMJ_LIST", cnt);

    • Marked as answer by pelegk1 Wednesday, February 20, 2013 1:18 PM
    Wednesday, February 20, 2013 1:16 PM
  • I think you forgot to set

    cmd.CommandType = CommandType.StoredProcedure;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, February 20, 2013 1:18 PM
  • As I previously mentioned, it is important to specify CommandType.StoredProcedure:

    cmd.CommandType = CommandType.StoredProcedure;

    The default is CommandType.Text so stored procedure parameters will not be passed correctly otherwise.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, February 20, 2013 1:21 PM
  • solved :

    cmd = new SqlCommand("casp_GetExist", cnt);

    changed to :

    cmd = new SqlCommand("casp_GetExist @BLMJ_LIST=@BLMJ_LIST", cnt);

    Note that this will execute the proc as text SQL statement instead of a stored procedure.  Although this method will work, it's not the right way to pass a TVP to a proc.  In fact, one can omit the parameter TypeName with the CommandType.StoredProcedure method.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, February 20, 2013 1:24 PM
  • i removed

    @BLMJ_LIST=@BLMJ_LIST

    and added :

    cmd.CommandType = CommandType.StoredProcedure;

    but now i dont get back recordset on line :

    SqlDataReader sqlDR = cmd.ExecuteReader();

    Wednesday, February 20, 2013 1:30 PM
  • You need the assignment for your case. Commandtype is to denote that the type of your execution as procedure.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, February 20, 2013 1:36 PM
  • Can you show again how your C# code look now? And also where did you create the table to pass.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, February 20, 2013 1:45 PM
  • i removed

    @BLMJ_LIST=@BLMJ_LIST

    and added :

    cmd.CommandType = CommandType.StoredProcedure;

    but now i dont get back recordset on line :

    SqlDataReader sqlDR = cmd.ExecuteReader();

     try
            {
                cmd = new SqlCommand("casp_GetExist", cnt);
                //command type is StoredProcedure
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter parameter = new SqlParameter();
                //The parameter for the SP must be of SqlDbType.Structured 
                parameter.SqlDbType = System.Data.SqlDbType.Structured;
                parameter.Value = dataTable;
                cmd.Parameters.Add(parameter); 
                ret = cmd.ExecuteScalar();
            }
    
    
    

    I tested the above and it works if I change your proc to return a count instead of a resultset (because you are using ExecuteScalar).  What is your actual proc code?



    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, February 20, 2013 1:49 PM
  • this is the updated code :

     SqlCommand cmd = new SqlCommand("casp_GetExist", cnt);
                SqlParameter parameter = new SqlParameter();
                cmd.CommandType = CommandType.StoredProcedure;
                //The parameter for the SP must be of SqlDbType.Structured 
                parameter.ParameterName = "@BLMJ_LIST";
                parameter.TypeName = "CampaignIdList"; 
                parameter.SqlDbType = System.Data.SqlDbType.Structured;
                parameter.Value = dataTable;
                cmd.Parameters.Add(parameter);
                SqlDataReader sqlDR = cmd.ExecuteReader();


    • Edited by pelegk1 Wednesday, February 20, 2013 2:23 PM
    Wednesday, February 20, 2013 2:23 PM
  • This looks OK to me. Can you also verify that dataTable is indeed populated? Also, can you trace with the profiler exact command send to SQL Server and try it in SSMS?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, February 20, 2013 4:55 PM
  • You may refer the below link:

    http://www.mssqltips.com/sqlservertip/2112/table-value-parameters-in-sql-server-2008-and-net-c/

    SqlConnection con;
    // modify connection string to connect to your database
    string conStr = "Server=localhost;Database=MSSQLTIPS;Trusted_Connection=True;";
    con = new SqlConnection(conStr);
     con.Open();
    using (con)
    {                
    // Configure the SqlCommand and SqlParameter.
    SqlCommand sqlCmd = new SqlCommand("dbo.InsertItemsTVP", con);
    sqlCmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvpParam = sqlCmd.Parameters.AddWithValue("@ItemTVP", _dt); //Needed TVP
    tvpParam.SqlDbType = SqlDbType.Structured; //tells ADO.NET we are passing TVP
    sqlCmd.ExecuteNonQuery();
     }
    con.Close();


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, February 20, 2013 5:06 PM
  • found the problem i closed the db connection before using the recordset.

    10X everyone

    Wednesday, February 20, 2013 5:06 PM