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?
 
Mark WillsConnect With a Mentor Topic 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
 
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
 
mlcktmguyAuthor Commented:
Perfect, thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.