Stored procedure returns int instead of result set

c# entity-framework sql

Question

I have a stored procedure that contains dynamic select. Something like this:

ALTER PROCEDURE [dbo].[usp_GetTestRecords] 
    --@p1 int = 0, 
    --@p2 int = 0
    @groupId nvarchar(10) = 0
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @query  NVARCHAR(max)

    SET @query = 'SELECT * FROM CUSTOMERS WHERE Id = ' + @groupId
    /* This actually contains a dynamic pivot select statement */

    EXECUTE(@query);
END

In SSMS the stored procedure runs fine and shows result set.

In C# using Entity Framework it shows returning an int instead of IEnumerable?

private void LoadTestRecords()
{
    TestRecordsDBEntities dataContext = new TestRecordsDBEntities();
    string id = ddlGroupId.SelectedValue;

    List<TestRecord> list = dataContext.usp_GetTestRecords(id); //This part doesn't work returns int
    GridView1.DataSource = list;
}

Generated function for usp_GetTestRecords

public virtual int usp_GetTestRecords(string groupId)
{
    var groupIdParameter = groupId != null ?
        new ObjectParameter("groupId", groupId) :
        new ObjectParameter("groupId", typeof(string));

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("usp_GetTestRecords", groupIdParameter);
}
1
30
8/15/2013 6:04:47 PM

Accepted Answer

Entity Framework can't tell what your stored procedure is returning. I've had success creating a table variable that mirrors the data from your SELECT statement. Just insert into the table variable then do a select from that table variable. EF should pick it up.

14
8/15/2013 3:45:13 PM

Popular Answer

I get this when I have a stored procedure that includes an "exec" call into a temporary table, such as:

insert into #codes (Code, ActionCodes, Description)
exec TreatmentCodes_sps 0

It appears that Entity Framework gets confused as to what should be returned by the procedure. The solution I've come across is to add this at the top of the sproc:

SET FMTONLY OFF

After this, all is well.



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