troubleshooting Question

return @rowcount from stored procedure

Avatar of Anthony Berenguel
Anthony BerenguelFlag for United States of America asked on
Microsoft AccessMicrosoft SQL Server
16 Comments1 Solution849 ViewsLast Modified:
hello,

I am somwhat new to stored procedures. What I want is to return the rowcount from the stored procedure to vba.

Here is my stored procedure code
GO
-- =============================================
-- Author:	aebea
-- Create date:        05/13/2011
-- Description:	returns recordset based on parameters 
-- =============================================
ALTER PROCEDURE [dbo].[sp_getRecIdsForProcessing]
	-- Add the parameters for the stored procedure here
	@act varchar(50),
	@beginDate datetime,
	@endDate datetime,
	@processStep int,
	@Rowcount int output	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @firstDate as datetime
	DECLARE @lastDate as datetime
	
	SET @firstDate = (SELECT CONVERT(DATETIME,@beginDate,101))
	SET @lastDate = (SELECT CONVERT(DATETIME,@endDate,101))	


		
    -- Insert statements for procedure here
    if @act = '*' 
			begin
				SELECT		tbl_ProcessTracking.RecID, tbl_ProcessTracking.OnThisProcStep, tbl_ProcessTracking.AsOf, tbl_MasterFile.PDFName, tbl_MasterFile.Act, 
							tbl_MasterFile.ActDate, tbl_MasterFile.FS_Name, tbl_MasterFile.DateReceived, tbl_MasterFile.CaseID, tbl_MasterFile.SheetComment, 
							tbl_MasterFile.Del_Rec
				FROM        tbl_MasterFile INNER JOIN
							tbl_ProcessTracking ON tbl_MasterFile.RecID = tbl_ProcessTracking.RecID
				WHERE		[ActDate] between @firstdate and @lastdate
							and [OnThisProcStep]=@processStep-1
			end
		else
			begin
				SELECT		tbl_ProcessTracking.RecID, tbl_ProcessTracking.OnThisProcStep, tbl_ProcessTracking.AsOf, tbl_MasterFile.PDFName, tbl_MasterFile.Act, 
							tbl_MasterFile.ActDate, tbl_MasterFile.FS_Name, tbl_MasterFile.DateReceived, tbl_MasterFile.CaseID, tbl_MasterFile.SheetComment, 
							tbl_MasterFile.Del_Rec
				FROM        tbl_MasterFile INNER JOIN
							tbl_ProcessTracking ON tbl_MasterFile.RecID = tbl_ProcessTracking.RecID
				WHERE		[ActDate] between @firstdate and @lastdate
							and [Act] = @act
							and	[OnThisProcStep]=@processStep-1
			end
			
	set @rowcount = @@rowcount
	RETURN @@rowcount
END

Open in new window


You can see i have set up a parameter called @rowcount. How do I set up my vba code to retrieve that value?

thanks!
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros