table value functions

  • 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?

  • Because you are declaring a parameter for the sp_executesql to use, but not actually using it in the query to execute...

    You can either change the last line to this:

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

    or this

    exec casp_GetExist @BLMJ_LIST=@p3;

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Maybe what you really need to do is post the C# code as that seems to be calling wrong...

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • fixed the C# 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();

    but dont get recordset on :sqlDR

  • have you captured a trace of that new C# code running?

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

    Viewing 5 posts - 1 through 4 (of 4 total)

    You must be logged in to reply to this topic. Login to reply