Stored Procedure with Datatable as param returns nothing

Question

I have set up a stored procedure which I am passing a data table into and calling directly from Entity Framework.

I have created a Type with the following sql:

 CREATE TYPE Regions AS TABLE 
             (      RegionId int, 
                    Region varchar(max),                    
                    BodyId int NULL, 
                    Body varchar(max),
                    AreaId int NULL, 
                    Area varchar(max),   
                    Location varchar(max), 
                    LocationId int
              )

My test stored procedure is as follows:

CREATE PROCEDURE [dbo].[GetStats]

     @regions  dbo.Regions READONLY 

AS
BEGIN

    SELECT * INTO #tmptble  from @regions  

    Select * from #tmptble


END

I am using the following to call the stored procedure:

 SqlParameter param = new SqlParameter();

            param.SqlDbType = SqlDbType.Structured;
            param.TypeName = "dbo.Regions";
            param.Value = myDataTable;
            param.ParameterName = "@regions";

return _context.Database.SqlQuery<RegionDetails>("GetStats", param);

My datatable is definitely the correct format as I have run this through profiler passing it in to the stored procedure and you can see all the inserts appearing. If I generate a test table from all the insert statements the procedure runs fine against that but when I run it with the passed in datatable it just returns no rows.

EDIT - for further info When I run this through profiler I get the following:

declare @p3 dbo.Regions

~~~a Load of insert statements of all my datatable data~~~

exec sp_executesql N'GetStats',N'@regions [dbo].[Regions] READONLY',@regions =@p3

UPDATE on the above I have been playing around with what is shown in profiler and if I replace

exec sp_executesql N'GetStats',N'@regions [dbo].[Regions] READONLY',@regions =@p3

with

EXEC GetStats @p3

Again it works. Has anyone got any clue why?

Well, if you posted the stored procedure it would be possible to answer this question. Right now it's impossible. — Zohar Peled, Mar 06 '17 at 16:02
I don't think the problem is anything wrong with my actual stored procedure, just the calling or passing the datatable as I literally switch out #tmptbl for TESTTable and it works. Is there any specific parts I can post that will help as I cannot post the whole procedure as it's very complicated I think will confuse things. — Bex, Mar 06 '17 at 16:04
Are you trying to insert rows into the passed datatable in the stored procedure? — Brandon Kramer, Mar 06 '17 at 16:06
I have updated the question with a test stored procedure. I am sending in a datatable as a param so I can insert it to a temporary table so I can join on it. It seems its not getting there correctly. — Bex, Mar 06 '17 at 16:09
This test procedure is meaningless. Try to either insert the values into a regular table or simply select the values from the table valued parameter and see if you get them back to the c# code. — Zohar Peled, Mar 06 '17 at 16:11
@eranotzap regions is my datatable, I am trying to insert it into a tmp table as per this stackoverflow post: http://stackoverflow.com/questions/29507629/passing-datatable-to-stored-procedure-as-an-argument — Bex, Mar 06 '17 at 16:21
@ZoharPeled I tried inserting my regions into a "proper" table using the stored procedure but there are no rows. I'm not sure how else to describe this, I know I'm missing something — Bex, Mar 06 '17 at 16:22
@eranotzap I thought that, but I have debugged and it's definitely got something in it and when I run profiler you can see all the data there — Bex, Mar 06 '17 at 16:23
Are your columns in the table in the same order as in the SQL table type definition? — Brandon Kramer, Mar 06 '17 at 16:23
This seems odd, but try removing the TypeName. Don't set it and see if that works. All the examples I see for Stored Procedures do not set TypeName. — Brandon Kramer, Mar 06 '17 at 16:30
@BrandonKramer Do you mean the typename in the c# or the stored procedure. According to MSDN https://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx you need to specify a type if its passing a datatable — Bex, Mar 06 '17 at 16:34
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/137363/discussion-between-brandon-kramer-and-bex). — Brandon Kramer, Mar 06 '17 at 16:35
One of the main points of a table valued parameter is you can join to it directly. There is no point in receiving a set of data and immediately copying to a temp table. Just join your paramter. — Sean Lange, Mar 06 '17 at 17:09

SteveD · Accepted Answer · 2017-03-06T17:07:15.757

1

Try this approach:

//create parameter
var param = new SqlParameter("@regions", SqlDbType.Structured);   
param.Value = myDataTable;   
param.TypeName = "dbo.Regions";  

//return result set
return _context.ExecuteFunction<RegionDetails>("dbo.Regions", param);

//OR
//execute stored procedure for inserts, returns rows effective
return _context.Database.ExecuteSqlCommand("exec dbo.Regions @regions", param);

edited Mar 06 '17 at 17:07

answered Mar 06 '17 at 16:14

SteveD

819
7
13

ExecuteSqlCommand seems only to return an int. Am I missing something? – Bex Mar 06 '17 at 16:18
@Bex - Revised answer to reflect your comments. – SteveD Mar 06 '17 at 17:08
I dont seem to have an execute function command on my context. Just execute sqlcommand. I have Entity Framework 6.1.1 – Bex Mar 07 '17 at 09:19
Worked it out! Thank you. I needed the parameter name in the call in Entity framwork like so: return _context.Database.SqlQuery("GetStats @regions", param); – Bex Mar 07 '17 at 10:15

Bex · Answer 2 · 2017-03-07T12:46:58.430

Answer based on @SteveD's answer but to clarify exactly what I did incase it helps any one else. There was nothing wrong with my stored procedure. It was purely my calling of it. It needed the parameter name in the actual call like so:

SqlParameter param = new SqlParameter();

            param.SqlDbType = SqlDbType.Structured;
            param.TypeName = "dbo.Regions";
            param.Value = myDataTable;
            param.ParameterName = "@regions";

return _context.Database.SqlQuery<RegionDetails>("GetStats @regions", param);

Stored Procedure with Datatable as param returns nothing

2 Answers2