set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ARResult]
	@StartDate DateTime = null,
        @EndDate DateTime = null,
	@AgencyList varchar(1000) = '',
	@Requestor varchar(50) = '',
	@UserID	INT,
	@AgencyID varchar(50) = null
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @SQL nvarchar(1200)
	DECLARE @ParamDefinition as NVARCHAR(2000)

	SET @SQL = 'SELECT
		min(h.DataHeaderID) as DataHeaderID, 
		min(h.CollectionDate) as CollectionDate,
		(SELECT TOP 1 DataLine.TestAndApproveDate FROM DataLine WHERE h.SpecimenNo = DataLine.SpecimenNo) as ApproveDate, 
		(SELECT TOP 1 DataLine.ResultFlag FROM DataLine WHERE h.SpecimenNo = DataLine.SpecimenNo ORDER BY ResultFlag DESC) as ResultFlag,
		min(h.SpecimenNo) as SpecimenNo, 
		min(h.SSN) as SSN, 
		min(h.LastName + '', '' + h.FirstName + '' '' + h.MiddleInitial) as FullName,
		(SELECT TOP 1 CASE 
			WHEN DataLine.Outcome = ''Inconsistent'' 
				THEN ''INCONSISTENT'' 
			ELSE ''CONSISTENT'' 
		END AS TMPOUT FROM DataLine WHERE h.SpecimenNo = DataLine.SpecimenNo ORDER BY TMPOUT DESC) as Outcome,		
		min(h.Requestor) as Requestor,
		min(h.AgencyNumber) as AgencyNumber
		FROM RxToxDataHeader h
		WHERE (1=1) '

		IF @AgencyList IS NOT NULL AND @AgencyList != ''
			SET @SQL = @SQL + ' AND h.AgencyNumber IN (' + @AgencyList + ')'
		IF @AgencyID IS NOT NULL AND @AgencyID != ''
		  BEGIN
			SET @AgencyID = '%' + @AgencyID + '%'
			SET @SQL = @SQL + ' AND h.AgencyNumber LIKE @AgencyID'
                END
		IF @Requestor IS NOT NULL AND @Requestor != ''
		  BEGIN
			SET @Requestor = '%' + @Requestor + '%'
			SET @SQL = @SQL + ' AND h.Requestor LIKE @Requestor'
		  END
		IF (@StartDate <> '') AND (@EndDate <> '')
			SET @SQL = @SQL + ' AND h.CollectionDate BETWEEN @StartDate AND DATEADD(day,1,@EndDate)'
		
		SET @SQL = @SQL + ' GROUP BY h.SpecimenNo'
		SET @SQL = @SQL + ' ORDER BY h.SpecimenNo DESC'
		
	
	SET @ParamDefinition = ' 
				@Requestor varchar(50),
				@StartDate DateTime,
				@EndDate DateTime,
				@AgencyList varchar(100),
				@UserID INT,
				@AgencyID varchar(50) '

	PRINT @SQL

	EXEC sp_ExecuteSQL @SQL, @ParamDefinition, @Requestor, @StartDate, @EndDate, @AgencyList, @UserID, @AgencyID
END

SET @SQL = 'SELECT
		min(h.DataHeaderID) as DataHeaderID, 
		min(h.CollectionDate) as CollectionDate,
		(SELECT TOP 1 DataLine.TestAndApproveDate FROM DataLine WHERE h.SpecimenNo = DataLine.SpecimenNo) as ApproveDate, 
		(SELECT TOP 1 DataLine.ResultFlag FROM DataLine WHERE h.SpecimenNo = DataLine.SpecimenNo ORDER BY ResultFlag DESC) as ResultFlag,
		min(h.SpecimenNo) as SpecimenNo, 
		min(h.SSN) as SSN, 
		min(h.LastName + '', '' + h.FirstName + '' '' + h.MiddleInitial) as FullName,
		(SELECT TOP 1 CASE 
			WHEN DataLine.Outcome = ''Inconsistent'' 
				THEN ''INCONSISTENT'' 
			ELSE ''CONSISTENT'' 
		END AS TMPOUT FROM DataLine WHERE h.SpecimenNo = DataLine.SpecimenNo ORDER BY TMPOUT DESC) as Outcome,		
		min(h.Requestor) as Requestor,
		min(h.AgencyNumber) as AgencyNumber
		FROM RxToxDataHeader h

SET @SQL = 'SELECT COUNT(*)
		FROM RxToxDataHeader h

DECLARE @RowCount BIGINT
--- Build Dynamic SQL Here
EXEC(@SQL)

SET @RowCount = @@ROWCOUNT

quote:

---

Originally posted by Lamprey

I'm pretty sure you can capture the @@ROWCOUNT after the EXEC. For Example:

DECLARE @RowCount BIGINT
--- Build Dynamic SQL Here
EXEC(@SQL)

SET @RowCount = @@ROWCOUNT

---

CREATE TABLE myTable99 (Col1 int IDENTITY(1,1), Col2 char(1))
GO

INSERT INTO myTable99(Col2) SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c'
GO

DECLARE @sql varchar(8000)
SET @sql = 'SELECT * FROM myTable99'
EXEC(@sql)
SELECT @@ROWCOUNT
GO

DROP TABLE myTable99
GO

...
BEGIN
	SET NOCOUNT ON;
	DECLARE @SQL nvarchar(1200)
	DECLARE @ParamDefinition as NVARCHAR(2000)
	DECLARE @intRowCount int

	SET @SQL = 'SELECT
...

...
	SET @SQL = @SQL + ' SELECT @intRowCount = @@ROWCOUNT'

	SET @ParamDefinition = ' 
				@Requestor varchar(50),
				@StartDate DateTime,
				@EndDate DateTime,
				@AgencyList varchar(100),
				@UserID INT,
				@AgencyID varchar(50),
				@intRowCount int OUTPUT '

	PRINT @SQL

	EXEC sp_ExecuteSQL @SQL, @ParamDefinition, @Requestor, @StartDate, @EndDate, @AgencyList, @UserID, @AgencyID, @intRowCount = @intRowCount OUTPUT'
	... Do something with @intRowCount here ...
END

quote:

---

Originally posted by Kristen
but that will still retrieve all the data rows, which is a bit wasteful if all you want is the count.

---

quote:

---

I need to get the total number of records returned from the stored procedure

---

DECLARE @rc int, @Error int, @Error_Message varchar(255), @Rowcount int

--Sample Execution 

EXEC usp_SEL_VENDOR_LIKE
  	   'PR' -- CompanyName
	 , null -- City
	 , null -- State
	 , null -- ZipCode
	 , 'X002548' -- @App_User
	, @rc OUTPUT, @Error OUTPUT, @Error_Message OUTPUT, @Rowcount OUTPUT

SELECT @rc AS int, @Error AS int, @Error_Message AS varchar, @Rowcount AS int