Return Number of Selected Records From a SROC.

I have a simple SPROC in my SQL Developer 14 DB that selects records to return to the calling program.

USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[aConvertspRead_bView_buildProd_TaxAuthorityRecs_02]    Script Date: 1/20/2018 12:26:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--
-- =============================================
Create PROCEDURE [dbo].[aConvertspbuildProd_TaxAuthorityRecs_02]
      -- Add the parameters for the stored procedure here
AS
BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      SELECT *
      From bView_buildProd_TaxAuthorityRecs_02
      Order By MuniCode, ControlNum, TaxType



END
I would like to return the number of records in the result set back to the calling program so I can show a processing status bar as the records are processed in the calling program.

How can I do that?
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You'll need to work with temporary tables since you can't SELECT FROM a stored procedure name, but you can INSERT INTO from a stored procedure result. Basically the steps are:
  1. Create the temporary table - in my example the WHERE 1=0 is just to return the column names and no records
  2. Insert results from the execution of the SP into the temporary table
  3. Count the number of the records in the temporary table
  4. Drop the temporary table
SELECT *
INTO #tmpTable
FROM bView_buildProd_TaxAuthorityRecs_02
WHERE 1=0

INSERT INTO #tmpTable
EXEC aConvertspbuildProd_TaxAuthorityRecs_02

SELECT COUNT(1) NumRecords FROM #tmpTable

DROP TABLE #tmpTable

Open in new window

0
Mark WillsTopic AdvisorCommented:
Use the OUTPUT  option as one of your parameters
 @SelectCount int OUTPUT

Open in new window

Then set it up in your stored procedure
set nocount on
select * from yourTable
set @selectCount = @@rowcount

Open in new window

Read : https://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
Perfect, thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.