Скачать или смотреть How to Get Row Count from a Stored Procedure with Multiple SELECT Statements

Learn how to retrieve the row count of a specific `SELECT` statement within a stored procedure in SQL Server without altering the procedure itself.
---
This video is based on the question https://stackoverflow.com/q/63127473/ asked by the user 'Lookup' ( https://stackoverflow.com/u/12041994/ ) and on the answer https://stackoverflow.com/a/63127582/ provided by the user 'vignesh' ( https://stackoverflow.com/u/3511840/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Stored procedure has multiple SELECT statements

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Get Row Count from a Stored Procedure with Multiple SELECT Statements

Stored procedures in SQL Server are essential for executing a group of SQL statements as a single unit. However, what happens when you have a stored procedure containing multiple SELECT statements and you need to obtain the row count of a specific statement without modifying the procedure? This guide will guide you through a quick and effective solution to this common problem.

Understanding the Problem

Imagine you have a stored procedure that retrieves information from an EMPLOYEE table. Here’s a simplified version:

[[See Video to Reveal this Text or Code Snippet]]

When you execute this stored procedure, it returns two result sets. However, you are interested in capturing the row count of the second SELECT statement to use it for further processing. The challenge lies in the fact that you cannot modify the stored procedure directly. What can you do?

The Solution: Using @ @ ROWCOUNT

The good news is that SQL Server provides a system function called @ @ ROWCOUNT. This function returns the number of rows affected by the last executed statement. Let’s see how you can leverage this to get the row count of the second SELECT statement.

Step-by-Step Guide

Execute the Stored Procedure: First, call the stored procedure as you normally would.

[[See Video to Reveal this Text or Code Snippet]]

Capture the Row Count: Immediately after executing the stored procedure, you can retrieve the row count of the last executed SELECT statement using the @ @ ROWCOUNT variable.

[[See Video to Reveal this Text or Code Snippet]]

Here’s how it looks in one code snippet:

[[See Video to Reveal this Text or Code Snippet]]

How It Works

The EXEC Test command executes the stored procedure, producing two result sets.

After executing the stored procedure, @ @ ROWCOUNT captures the number of rows returned by the last SELECT statement within that stored procedure, which is handy for our needs.

Important Notes

Order of Execution: Always keep in mind that @ @ ROWCOUNT reflects the row count of the last statement executed before it's called. In our case, since we're calling it immediately afterward, it will provide the count of the second SELECT.

No Modifications Required: The approach allows you to retrieve data without making any changes to the original stored procedure, preserving its integrity.

Conclusion

Retrieving the row count of a specific SELECT statement in a stored procedure is straightforward when you use the @ @ ROWCOUNT function. By following the steps outlined in this blog, you can efficiently gather the required data without any alterations to the procedure itself. This method not only saves time but also keeps your database routines clean and maintainable.

By mastering the use of @ @ ROWCOUNT, you can enhance your SQL Server interactions and perform more advanced data manipulations in your applications.