Insert stored procedure into table problem

silvestri_optima · January 21, 2019, 11:01am

Hello,
I’m having some troubles with the mapping of an sql server stored procedure into a table. I would like to have some clarifications about this matter. Here attached you’ll find a test procedure.

If the input is null, it generates an exception, otherwise it inserts a row in a log table ad returns the resultset.
The import of sp doesn’t work and generate an error (2 times).

I also attached the profile of the SQL server

The procedure has been executed in a regular way for 2 times!
The errors occured from the exception that shows up when the procedure runs with null. So I am assuming that the procedure has not simply been imported but it runs twice.
For this reason, I’ve modified the store procedure by removing the control, in order to have the procedure running until the end.

Here it shows that everything worked out well. The import worked.

Since everything was working well, what I did was to check the log table inside the store procedure (that table was emptied before).
The result is that in the table you can find a row inserted by the import of the store procedure that I’ve just run.
For this reason I am worried because i’m sure that if I had more complex work to do or if I had some delete, the import would modify or lose data.

The profiler of the sql server shows that the procedure run just once.
If I modify the procedure (as I mentioned and showed in proc1) by putting insert before the exception, I will see two insert into log table

So Iwhat i’m asking is why does the procedure run during the import? And if this supposition is correct, how do I need to proceed? What shall i do to be able to import this type of procedure without errors?

Thank you,
Andrea

antonk · January 21, 2019, 2:44pm

Hello

Schema Modeler need to run the procedure to determine the schema of its result (ie field names, their types etc).

Actually Schema Modeler can bind a Stored Procedure in 2 different ways:

As a Schema Table. In this case it is assumed that a stored proc returns a dataset, so Schema Modeler need to execute this stored proc to determine the schema of this dataset
As a Schema Command. In this case it is assumed that this stored proc modifies data and doesn’t return any datasets. So Schema Modeler queries the database for parameter types of this stored procedure, but doesn’t execute it directly.

During development (or at least during initial Schema creation) use a modified Stored Procedures: if all passed in parameters are set to NULL then instead of raising an error or doing any data modifications or complex operations just return an empty data table with the same structure that the actual data will have.

Regards