First, what is SET NOCOUNT ON? The description in this microsoft doc https://docs.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql?view=sql-server-ver15
is:
"Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set."
further it remarks:
"When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned."
By default SET NOCOUNT is OFF.
Some stored procedures may contain several statements (SELECT/INSERT/UPDATE/DELETE etc.). When these are called in Sql Server Management Studio the result sets are typically displayed on the Results tab and rows affected counts on the Messages tab (often ignored). However, when called via another application using JDBC the results and rows affected counts may not be returned as expected. Sometimes appearing that the stored procedure called from JDBC returns null. However, this is likely the result of how the JDBC api's are used to retrieve the returned results. Google "JDBC and SET NOCOUNT ON" to learn more on this topic. QIE uses JDBC under the covers. As of version .51 the pQuery.doQuery() and the pQuery.callStoredProcedure() methods have been designed to work with these type of stored procedures.
Recommendations:
The pQuery.doQuery() method ignores the returned update counts and returns the first result set as a CSV Message. Use with stored procedures that return a single result set.
The pQuery.callStoredProcedure() method ignores the returned update counts and returns all result sets as an array of CSV Messages. Use with stored procedures that return multiple result sets. See Using a Stored Procedure that Returns Multiple Result Sets in QIE?
Additional examples can be found in this question:
How to call a stored procedures in QIE?
