table value functions

Question

table value functions

peleg

SSCrazy

Points: 2611
More actions
February 20, 2013 at 5:09 am

#268891

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

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

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);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

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);

peleg SSCrazy Points: 2611 More actions · Answer 3

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

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

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

MM

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