Stored procedure with static data not returning values

Question

I have the following stored procedure:

CREATE PROCEDURE [dbo].[usp_GetReportLevel]
AS
    DECLARE @TEMP TABLE (BK_REPORT_LEVEL INT, REPORT_LEVEL_NAME VARCHAR(MAX))

    INSERT INTO @temp 
    VALUES (0, 'Client Group'), (1, 'Client'), (2, 'Related Matter'), (3, 'Matter')

    SELECT * 
    FROM @TEMP 
GO

The entity database like this:

    public IEnumerable<usp_GetReportLevel_Result> usp_GetReportLevel()
    {
        //const string storedProcedure = "usp_GetReportLevel";

        var result = Database.SqlQuery<usp_GetReportLevel_Result>($"EXEC usp_GetReportLevel");
        return result;
    }

The usp_GetReportLevel_Result as this:

public class usp_GetReportLevel_Result
{
    [Column("BK_REPORT_LEVEL")]
    public int BK_REPORT_LEVEL;
    [Column("REPORT_LEVEL_NAME")]
    public string REPORT_LEVEL_NAME;
}

and the c# code in a constructor to select the list items from the stored procedure as this section:

    List<SelectListItem> reportLevelList2= new List<SelectListItem>();

    var reportLevelusp = context.usp_GetReportLevel();

    foreach (var reportLevel in reportLevelusp)
    {
        SelectListItem lvl = new SelectListItem()
        {
            Text = reportLevel.REPORT_LEVEL_NAME,
            Value = reportLevel.BK_REPORT_LEVEL.ToString()
        };
        reportLevelList2.Add(lvl);
    }

    ReportLevelList = reportLevelList2;

Now if I debug that code, the foreach will enter 4 times, telling me that it is reading the database values right, but the values BK_REPORT_LEVEL and REPORT_LEVEL_NAME are always 0 and null respectively.

Why is this happening? The stored procedure is returning what I want to expect if I execute it on the database, Why Linq isn't getting the correct data? I can't see the error

Try adding SET NOCOUNT ON to the start of your procedure, does that help? — DavidG
– DavidG, Commented Jul 10, 2018 at 14:15
I assume you added it before the INSERT but after the AS? — DavidG
– DavidG, Commented Jul 10, 2018 at 14:19
I try it after the AS and after the DECLARE @TEMP TABLE, neither worked — Ragmar
– Ragmar, Commented Jul 10, 2018 at 14:21
Though this should really be in a table, not in a stored proc. — DavidG
– DavidG, Commented Jul 10, 2018 at 14:26

Brad · Accepted Answer · 2018-07-10 14:24:06Z

1

I have had issues with Selecting from Temp tables or table variables from SQL SP's before. Depends on what version of SQL Server you are dealing with. You need to use with result set:

https://www.mssqltips.com/sqlservertip/2356/overview-of-with-result-sets-feature-of-sql-server-2012/

Convert your call to use this:

EXEC usp_GetReportLevel
WITH RESULT SETS
(
 ( 
  BK_REPORT_LEVEL INT, 
  REPORT_LEVEL_NAME varchar(max)
 ) 
)

answered Jul 10, 2018 at 14:24

Brad

3,7253 gold badges32 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ragmar Over a year ago

Assuming that you referred changing the entity database procedure to make a call like that, it is still returning the same values. If you mean changing anything else, I haven't try it yet.

Ragmar Over a year ago

also, this is the info of the sql server: Microsoft SQL Server Management Studio 14.0.17230.0

user10038293 · Accepted Answer · 2018-07-10 14:34:45Z

0

Try ExecuteSqlCommand:

  _context.Database.ExecuteSqlCommand("myproc");

Or with a return parameter:

 SqlParameter stmt = new SqlParameter("@stmt", System.Data.SqlDbType.Varchar);
 stmt.Direction = System.Data.ParameterDirection.Output;

 _context.Database.ExecuteSqlCommand("myproc @stmt out", stmt);
 var qry = stmt.value;

Make sure to put the out parameter in the procedure as well.

answered Jul 10, 2018 at 14:34

user10038293

1 Comment

user10038293 Over a year ago

@Ragmar how about ToList(); like: var result = Database.SqlQuery<usp_GetReportLevel_Result>($"EXEC usp_GetReportLevel").ToList();

Ragmar · Accepted Answer · 2018-07-10 15:54:22Z

0

Didn't have to change anything, all was all right. I just needed to add { get; set; } on the variables....

public class usp_GetReportLevel_Result
{
    [Column("BK_REPORT_LEVEL")]
    public int BK_REPORT_LEVEL { get; set; }
    [Column("REPORT_LEVEL_NAME")]
    public string REPORT_LEVEL_NAME { get; set; }
}

answered Jul 10, 2018 at 15:54

Ragmar

3705 silver badges22 bronze badges

Collectives™ on Stack Overflow

Stored procedure with static data not returning values

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related