Avatar of mlcktmguy
mlcktmguy
Flag for United States of America asked on

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?
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
mlcktmguy

8/22/2022 - Mon
Vitor Montalvão

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

ASKER CERTIFIED SOLUTION
Mark Wills

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
mlcktmguy

ASKER
Perfect, thank you.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23