0

I'm having trouble calling stored procs in ASP.NET Core 3.0. Some of the ones I've called have worked, but I found one that won't, and I haven't seen a lot of online documentation that addresses this (in 3.0, uses ExecuteSqlInterpolatedAsync and ExecuteSqlRaw in place of methods such as FromSql).

The stored procedure is a simple one (it just calls another sproc and returns on int):

ALTER PROCEDURE [dbo].[getsystemnumber](@p_controlid varchar(8),
                 @p_alternateid varchar(8))

AS
DECLARE @return_number  int

-- Requests a block of 1 number
EXECUTE @return_number = dbo.getsystemnumber_gateway @p_controlid, @p_alternateid, 1

RETURN @return_number

It's a long standing sproc that is used daily by older applications and works fine, and if I run it manually it works fine.

I'm trying to create a simple method that can be called whenever the application needs a new Id

        public async Task<int> getNewId()
        {
            using (TMW_LiveContext context = new TMW_LiveContext())
            {
                FormattableString query = $"EXEC dbo.getsystemnumber @p_controlid = 'fgtbycmp', @p_alternateid = ''";
                CancellationToken cancellationToken = default;
                int newId = await context.Database.ExecuteSqlInterpolatedAsync(query, cancellationToken);

                return newId;
            }

        }

Which is called like so

int newId = getNewId().Result;

The problem I am encounter is that is always returns a value of 1, no exceptions. I have also tried the non-asynchronous call (context.Database.ExecuteSqlInterpolated(query)) and using ExecuteSqlRaw. I always get 1 as a result. But if I run the sproc in SQL server, I get the values I expect (values looking like this: 13798303). What am I doing wrong? How can I get it to return the proper integer values?

EDIT - One thing to note is that the sproc does two things: It returns the sys_controlnumber from systemcontrol and then in increments sys_controlnumber. The latter is working, which is good, so I know it's calling the sproc, but I can never get it to return the sys_controlnumber, which is equally important.

David
  • 43
  • 10
  • Make sure you are actually connected to the right database. Behaviour seems like you are connecting to a different instance of the database. Other possibility is that you use a different user name to connect to the database and that user sees different data. – NineBerry Nov 27 '19 at 23:11
  • I checked the database connection. It appears to be good. – David Nov 27 '19 at 23:48
  • One thing to note is that the sproc does two things: It returns the sys_controlnumber from systemcontrol and then in increments sys_controlnumber. The latter is working, which is good, so I know it's calling the sproc, but I can never get it to return the sys_controlnumber, which is equally important. – David Nov 27 '19 at 23:51
  • The function you use return the count of modified rows. See https://stackoverflow.com/questions/35631903/raw-sql-query-without-dbset-entity-framework-core for alternatives – NineBerry Nov 28 '19 at 02:26
  • Yes. You would need to bind an output parameter to get the result. But returning data in the return value of a stored procedure is a bad practice anyway. The standard practice in SQL Server is that the return value of a stored procedure should only indicate success or failure, not return data. – David Browne - Microsoft Nov 28 '19 at 14:16
  • I agree returning value of a stored procedure is a bad practice. Regrettably, I have to leave that stored procedure as-is because it was created and used by a third party app. I ended up getting the data another way other than trying to use a stored procedure. – David Dec 09 '19 at 18:44

2 Answers2

0

I ended up not using the stored procedure to get the data. As was pointed out, this is a bad practice. This particular stored procedure was created by and being used to retrieve the data in a third party desktop app, so my initial thought was to do the same, but it doesn't work in ASP.NET Core 3.0. I found a different way to query the data and used a stored procedure to increment the value (increment the value is the other thing the original stored procedure was doing).

David
  • 43
  • 10
0

NineBerry was correct in that you would always get a 1 value because ExecuteSqlInterpolatedAsync always returns the amount of rows affected. You would have to provide an OUT parameter.

The SQL procedure would need to be written with an out parameter for the OUT parameter to work though. If you do not want to alter the procedure because it is being used by other systems, then write a wrapper procedure that would call this procedure and assign the result to an OUT parameter with the same name as the OUT parameter in the argument specified in the ExecuteSqlInterpolatedAsync method.

DoubleAxion
  • 41
  • 6