How to know if a stored procedure that is executed within another procedure, returns an error, exception or empty from C #?

Question

How to know if a stored procedure that is executed within another procedure, returns an error, exception or empty from C #?

Navigation

#1 by (1 votes)

1

In this code I show only one (EXAMPLE) where I have a Stored Procedure and within it I get data from another Procedure, How can I capture the value that returns this procedure or the output values (sp_SearchTurn) before getting the one that returns the procedure (sp_Process) .

That is, how to access the output values of the secondary procedure that are not declared in the main parameters of the procedure (sp_Process)

I get the output values well and use them within the main procedure, but I need to obtain those output values that belong to the secondary procedure in c #, in order to inform the user if something happened during the process. (As a Log) if the information I'm looking for in the internal procedure was not found.

In C # I tried adding these parameters as output, but since they do not belong to the parameters of the main procedure when executing, the exception is that the stored procedure has more arguments than those specified.

NOTE: The storage procedure works perfectly, I just need to get those output values from the secondary procedure in c # (I DO NOT HAVE TO MODIFY THE STORAGE PROCEDURES)

Example:

    ALTER PROCEDURE [dbo].[sp_Process] (
    @id numeric, 
    @type_time      char(1)
 )
As

declare     @terminal       varchar(30),
    @id_employee    numeric,
    @hour           datetime,
    @date       datetime,
    @inic           numeric

set  @terminal =   host_name()
set  @inic = convert(numeric,replace(left(right(convert(varchar(80),getdate(),9),14),12),':',''))

-- El otro procedimiento (Estos valores necesito capturarlos en c#)
exec sp_SearchTurn    @hour,
                     @turn  output ,
                     @date_valid output

print @turn

Declare @period  numeric(10)
Select  @period = period from setup
            where convert(char(10),date_valid,111)=convert(char(10),@date,111)  and id_turn = @turn 

-- El codigo del procedimiento continua esto solo es un ejemplo
-- El codigo del procedimiento continua esto solo es un ejemplo
-- El codigo del procedimiento continua esto solo es un ejemplo

c# sql-server stored-procedure

asked by J. Rodríguez 11.12.2017 в 20:48

source

1 answer

Error parsing json in android: t user of type org.json.JSONObject can not be converted to JSONArray Print username when logging in

score 1 · Accepted Answer

After a lot of research I found this method and I will adapt it to work with what I need.

Capturing the print, message or output values of the Sql Server driver (in this way I will create a Log of messages and perform the action I want.)

With the Event: SqlInfoMessageEventArgs

 myConnection.InfoMessage += new SqlInfoMessageEventHandler(myConnection_InfoMessage);

 void myConnection_InfoMessage(object sender, SqlInfoMessageEventArgs
e) { myStringBuilderDefinedAsClassVariable.AppendLine(e.Message); }

Credits to: @AdaTheDev

More information:

link