Stored Procedure returns no columns, only integer

Question

I have a problem with the entitry framework 6.0.0.0 when I import the stored procedure no matter what I do it does not return the result set, instead it returns only integer or set to none. below is the sp.

alter proc spGetProd
@Prod nvarchar(500)
as
begin
SET FMTONLY OFF
IF OBJECT_ID('tempdb..##PRODUCTTABLE') IS NOT NULL DROP TABLE ##PRODUCTTABLE
DECLARE @MYQUERY nvarchar(MAX), @my_Div nvarchar(500);
set @my_Div = REPLACE(@Prod, ',', ''',''');

SET @MYQUERY = 'SELECT DISTINCT [GNo],[GName]    
into ##PRODUCTTABLE FROM ABC
where Div IN ('''+@my_Div+''') 
order by GNo'

EXEC (@MYQUERY)
SELECT GNo, GName FROM ##PRODUCTTABLE;
drop table  ##PRODUCTTABLE;
end

No matter what I do whether I set SET FMTONLY OFF / ON, NO WORK. I did it long back and it worked for one time only, when I set SET FMTONLY OFF and then removed it and it worked for that sp but for other Stored Procedures its not working. Even if I get the result set from select * from ##PRODUCTTABLE; or specify the columns like above. Here is my Action in MVC.

public JsonResult GetData()
        {
         var allProducts = gentity.spGetProd("AB"); //return type shows as int.
         return Json(allProducts, JsonRequestBehavior.AllowGet);
       }

Is there any other workaround because my app is mostly dependent on the stored procedures which usually return data from temp tables like above.

I don't work with entity framework but I'm sure it has some way of dealing with table valued parameters. Don't send comma delimited strings to your stored procedure, send a table valued parameter instead. This will eliminate the need for dynamic sql and using a temporary table, among many other benefits. — Zohar Peled, Jan 29 '17 at 12:03

score 2 · Answer 1 · edited May 23 '17 at 12:01

2

EF can't derive the structure of the result set from the stored procedure, because it is composed dynamically. But you can fix that and at the same time simplify the stored procedure.

First, create a Split function, for example, this one. Then use it in your stored procedure:

...
BEGIN
    SELECT DISTINCT [GNo],[GName]    
    FROM ABC
    JOIN dbo.Split(@Prod) AS prod ON prod.Name = ABC.Div 
    order by GNo
END

Now EF will be able to infer the returned columns from the SELECT statement.

edited May 23 '17 at 12:01

Community

1
1

answered Jan 29 '17 at 13:31

Gert Arnold

105,341
31
202
291

Is there any other simple way to work with IN operator inside stored procedure. like without enclosing inside the apostrophe. **SET @MYQUERY = 'SELECT DISTINCT [GNo],[GName] into ##PRODUCTTABLE FROM ABC where Div IN ('''+@my_Div+''') order by GNo'** Because I would be using this technique in more than one stored procedure. – Khan Jan 29 '17 at 13:45
That would require a construction in which the `IN` statement can be parametrized. There are [ways to do that](http://stackoverflow.com/q/337704/861716) but not as easy as splitting the string into a table variable. – Gert Arnold Jan 29 '17 at 18:32

score 1 · Accepted Answer · answered Jan 29 '17 at 14:25

The problem is in your stored procedure. Your problem is very simple and you're making the stuff complex.

Just create this function in your database(s).

CREATE FUNCTION dbo.SplitStrings
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO

and now your stored procedure should look like

ALTER PROC spGetProd
    @Prod nvarchar(500)
AS
BEGIN
 SET NOCOUNT ON;
 SELECT DISTINCT [GNo],[GName]    
 FROM ABC
 WHERE Div IN (SELECT Item FROM dbo.SplitStrings(@Prod,','))
 ORDER BY GNo;
END

No temp tables, no extra variables, no EXEC, no objects dropping. Just a clear select statement for your EF.

**Igor Micev**, you just saved my life with your solution. Really thanks alot for this. It will help a lot for EF to get columns. Really appreciate Thanks again. — Khan, Jan 30 '17 at 04:08

Alexei - check Codidact · Answer 3 · 2017-01-29T12:30:51.410

It is not clear how you actually execute your procedure (dynamically or you use some kind of mapping). If I remember correctly, I had some problems related to resultset structure (schema) in SQL Server 2012 and I had to use WITH RESULT SETS to explicitly tell the expected resultset structure.

In Entity Framework this can be accomplished by this:

var query = "EXECUTE spGetProd @Prod WITH RESULT SETS ((GNo INT, GName NVARCHAR(1024)))";
var result = context.Database.SqlQuery<EntityType>(sql).ToList();

Edit after feedback

If result set structure is dynamic, I do not think Entity Framework is appropriate here. Instead, get the results into a DataSet.

If the maximal structure in known, you can transfer DataSet information into a list of objects:

public static DataTable ConvertTo<T>(IList<T> list)
{
    DataTable table = CreateTable<T>();
    Type entityType = typeof(T);
    PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);

    foreach (T item in list)
    {
        DataRow row = table.NewRow();

        foreach (PropertyDescriptor prop in properties)
        {
            row[prop.Name] = prop.GetValue(item);
        }

        table.Rows.Add(row);
    }

    return table;
}

but how about the dynamic columns like pivot which can have n number of columns. — Khan, Jan 29 '17 at 12:14
I am using my sp to return result set to var set; which then will return data as json format using api as jsonresultset. — Khan, Jan 29 '17 at 12:15

Stored Procedure returns no columns, only integer

3 Answers3