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.
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.
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.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
