Stored Procedure returns no columns, only integer

asp.net-mvc c#-4.0 entity-framework-6 sql-server tsql

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.

1
0
1/29/2017 12:42:34 PM

Accepted Answer

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.

1
1/29/2017 2:25:59 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow