Stored procedure issue

Data Abstract
1

Hi,

I’ve just changed a lengthy query which I had in a data table in a schema over to a stored procedure.
All of the parameters and resulting columns are identical.

This all seems fine in the schema and I can preview the data and it executes without any problems.

When I try to run it via my code though, I get an error that the stored proc expected a parameter and it shows the first parameter name. This is what happens if you try executing a stored procedure without any parameters supplied at all.

I should note that, most of the time, most of the parameters are actually NULL but this is valid and the stored procedure accepts this. It’s almost like the code isn’t supplying any parameters to the stored procedure.

I feel like I’m doing something stupid but I can’t quite figure out what.

2

can you show, how do you open this SP in your code?

3

Further, I found this article: Schema Modeler and Stored Procedures (again)

This seems to suggest that I can’t simply treat the schema table as a normal one, setting parameters and then opening it, but instead have to use different means to access the results.

If so then this seems nuts to me. Surely the whole point of DA is to abstract the database and the client shouldn’t care whether the data is being provided by automatically generated SQL, hardcoded SQL or a stored procedure. The schema has all the necessary information to execute the stored procedure with the correct parameters and return a dataset.

4

Sorry, we were typing at the same time.

I’m simply trying to access it as if it were still a hardcoded query in the schema.

5

what platform you are using (Delphi or .NET)?

6

Delphi (10.2 Tokyo) with DA 9.3.105.1351

7

in general, there should be no problem with opening SP if it provides dataset.
if you replace complex query with view instead of SP, will it work as expected?

8

Ok, just for clarity, here’s exactly what I did.

I had a rather complex query using a lot of CTEs which was coded directly into the schema and had a total of 14 parameters and returned a dataset with three columns. This all worked fine, all I did in code was set the required parameters and open the table.

For performance reasons I’ve now converted this into a stored procedure. This proc takes the exact same 14 parameters and returns the same three-columned dataset.

All I did in the schema was change the Statement Type to StoredProcedure, remove the old SQL and then select the stored procedure name from the drop-down list against Stored Procedure Name. Everything else was left alone.

If I go to the Preview tab in the modeler and hit Execute then it appears to run without errors but doesn’t return any rows.

My client code is unchanged and is simply setting the parameters (or leaving them NULL) and then opening the schema table, but this is when the aforementioned error occurs.

Should what I’m doing work or am I missing something? I’m au fait with stored procedures but have never tried using them with DA before.

9

can you create a simple testcase, pls?
you can attach it here or post directly to support@

10

Yeah I’ll rig one up but will probably be Monday now

11

Have emailed a test case through showing what I’m trying to do. There’s an SQL script in there to create the database.

I’m using MSSQL and FireDAC.

12

as temporary workaround, you can use custom SQL like

exec TestProc :MinId, :MaxId
13

Thanks, logged as bugs://80381

14

Ok, so do I change the schema from the stored procedure setting to manual SQL and just use that syntax?

15

if you drag stored procedure to tables section, it will create such syntax: EXEC [SP_NAME] :params for MSSQL database.

StoredProcedure type is used for .NET drivers and it causes some problems in Delphi drivers.
I can recommend to create such tables via drag&drop tables/views/stored procedures into table section.

16

Ah ok - I tried dragging it across but it created a command instead, I didn’t realise I could drop it specifically on the tables section.

17

bugs://80381 got closed with status nochangereq.