0

I am trying to create a stored procedure to achieve the following in the stored procedure:

CREATE PROCEDURE [dbo].[spByTableNameReturnLastRowNew]
    @tableName nvarchar(50)
AS
BEGIN
    DECLARE @SelectLastRow NVARCHAR(100)

    SET @SelectLastRow = 'SELECT TOP 1 * FROM ' + QUOTENAME(@tableName)+ ' ORDER BY AutoNo DESC'

    EXECUTE(@SelectLastRow)

    RETURN 
END

it is work but when calling it by entity framework the result Variable return INT datatype I want to return row (object) to use data not int.

This is the code using the stored procedure in Entity Framework:

var result = milestoneArhEntities.spByTableNameReturnLastRowNew(TableName);
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    This won't work. * The return value of a stored procedure is always an integer. This is a completely different thing to a resultset selected from a stored procedure. * `var` is a compile time instruction for the compiler to work out what type a variable is and bake that type into the compiled program; you can't vary it at runtime * Entity Framework is intended to map C# objects to database side rows, and for that a consistent mapping is required. If you're returning a differently shaped row all the time, you're going to struggle. Using Dapper would probably be easier – Caius Jard Jan 25 '21 at 08:23
  • use SQLQuery or make a temporary table in the procedure and then use that table in EF model – Ahmed Jan 25 '21 at 08:25
  • Thank you, if i want Stored Procedure to Take Table Name in Runtime and return Last row in This Table what do ? –  Jan 25 '21 at 08:26
  • It might work with something more like `var result = context.TableNameEntity.FromSqlInterpolated($"spByTableNameReturnLastRowNew '{TableName}', ")` but your result is typed at compile time. Might have to use generics if youre trying to write a single C# side method that returns any entity – Caius Jard Jan 25 '21 at 08:26
  • Why do you (think you) need to retrieve the last row from any table? It's almost always a bad idea - e.g. if you need to find what identity value it was assigned, there are better ways. If you're trying to implement identity yourself, there are better ways, etc. – Damien_The_Unbeliever Jan 25 '21 at 08:51
  • And bear in mind that it's unusual to build a system that only supports one user - if you've got multiple users, you've got no guarantee of how the current last row in the table relates to either recent inserts or any future ones for one specific user. – Damien_The_Unbeliever Jan 25 '21 at 08:53
  • I want to return the last row depending on the column identity in Table –  Jan 25 '21 at 09:04
  • @marc_s in OP's defence: there is a presumably consistent ordering shown in the SP: `ORDER BY AutoNo DESC`, which I presume is actually an `IDENTITY` or similar – Marc Gravell Jan 25 '21 at 09:54

1 Answers1

0

The ORM here needs to understand (in advance) the table layout in order to interpret result grids as objects. Because of this, you can't really do:

var result = milestoneArhEntities.spByTableNameReturnLastRowNew(TableName);

because the ORM can't possibly know what that means; an individual method can usually be configured to return, say, a Order (or List<Order>) or a Customer (or List<Customer>) etc, but that doesn't really help you here since the table structure is defined solely by the tableName parameter.

There's no good way to do what you want here, but I'm not convinced that what you want is a useful thing anyway, in the general sense. I'm also not sure that an SP is doing much for you here, compared to inline SQL.

You could write multiple methods i.e.

var customer = milestoneArhEntities.GetLastCustomer();
var order = milestoneArhEntities.GetLastOrder();

with them being table/class specific. The ORM should be fine with that.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900