return @rowcount from stored procedure

Anthony Berenguel
Anthony Berenguel used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
>RETURN @@rowcount
this line is not needed

anyhow, see here on how to run properly stored procedure, with output parameters:
http://msdn.microsoft.com/en-us/library/aa224819%28v=sql.80%29.aspx
HainKurtSr. System Analyst

Commented:
this is it:

set @rowcount = @@rowcount

but you have it already, other line is not necessary, you use it in functions...

you may want to convert it into function

CRETAE FUNCTION [dbo].[sf_getRecIdsForProcessing](
      -- Add the parameters for the stored procedure here
      @act varchar(50),
      @beginDate datetime,
      @endDate datetime,
      @processStep int) returns int
AS
BEGIN
... same code
return @@rowcount
END;

Author

Commented:
angelIII,
thanks for letting me know i have uneeded code out there. I'm really new to stored procedures so thanks for the heads up.

HainKurt,
is it possible to get @rowcount to be returned with creating a function? If so, how do i set up my vba code to grab the value?

thanks all!

aebea
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

HainKurtSr. System Analyst

Commented:
post your current vba code
HainKurtSr. System Analyst

Commented:
I guess instead of

command.open/execute....

you just say

dim r as integer = command.executeScalar()
HainKurtSr. System Analyst

Commented:
and of course you commant out the output parameter, you dont need that if you convert it to function...

Author

Commented:
Public Function sp_getRecIdsForProcessing(sqlConnection, this_rst, this_act, this_fstDay, this_lstDay, this_process_step, this_rst_count)
    Dim connectionString As String
    Dim category As String
    Dim category_searchString As String
    
    'establish connection
    Set sqlConnection = New ADODB.Connection
    connectionString = "DRIVER={sql server};DATABASE=RDITS_build020310; SERVER=SBODWH\SQLEXPRESS; Trusted_Connection=Yes;"
    sqlConnection.connectionString = connectionString
    sqlConnection.Open
    
    'set recordsets
    Set this_rst = New ADODB.Recordset
    
    'set commands
    Set getRecordSet = New ADODB.Command
    
    'open the recordset
    Debug.Print category_searchString
    Debug.Print actDate
    Debug.Print fs_name
    With getRecordSet
        .ActiveConnection = sqlConnection
        .CommandType = adCmdStoredProc
        .CommandText = "sp_getRecIdsForProcessing"
        .Parameters("@act").Value = this_act
        .Parameters("@beginDate").Value = this_fstDay
        .Parameters("@endDate").Value = this_lstDay
        .Parameters("@processStep").Value = this_process_step
        .Parameters("@rowcount").Value = 0
    End With
    
    ' get recordset
    Set this_rst = getRecordSet.Execute

End Function

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
you don't define the parameters correctly,
please read the article I linked above, it shows how to do.
HainKurtSr. System Analyst

Commented:
comment out these lines

   Set this_rst = New ADODB.Recordset

and this

   .Parameters("@rowcount").Value = 0

update this as:

Set this_rst = getRecordSet.Execute
-->
dim rn as integer = getRecordSet.ExecuteScalar()

then check the value of rn, if it is 0 nothings is inserted/updated...

Author

Commented:
HainKurt,

I can do this without modifying more stored procedure?

thanks!

Author

Commented:
HainKurt,

vba does not like this dim rn as integer = getRecordSet.ExecuteScalar()
Top Expert 2012
Commented:
Use somthing like the function below.  You would then call it as follows:

Dim this_rst As Integer

this_rst = sp_getRecIdsForProcessing("<Some act goes here>", "<Some Begin Date>", "<Some End Date>", <SomeProcessStepGoesHere>)
Public Function sp_getRecIdsForProcessing(this_act As String, this_fstDay As Date, this_lstDay As Date, this_process_step As Long)
Dim sqlConnection As ADODB.Connection
Dim getCommand As ADODB.Command
Dim connectionString As String

'establish connection
Set sqlConnection = New ADODB.Connection
connectionString = "DRIVER={sql server};DATABASE=RDITS_build020310; SERVER=SBODWH\SQLEXPRESS; Trusted_Connection=Yes;"
sqlConnection.connectionString = connectionString
sqlConnection.Open

'set commands
Set getCommand = New ADODB.Command

'open the Command object
With getCommand
    .ActiveConnection = sqlConnection
    .CommandType = adCmdStoredProc
    .CommandText = "sp_getRecIdsForProcessing"
    .Parameters.Append .CreateParameter("@act", adVarChar, adParamInput, 50, this_act)
    .Parameters.Append .CreateParameter("@beginDate", adDBTimeStamp, adParamInput, 8, this_fstDay)
    .Parameters.Append .CreateParameter("@endDate", adDBTimeStamp, adParamInput, 8, this_lstDay)
    .Parameters.Append .CreateParameter("@processStep", adInteger, adParamOutput, 4)
    .Execute , , adExecuteNoRecords
    sp_getRecIdsForProcessing = .Parameters("@rowcount").Value
End With
Set getCommand = Nothing

sqlConnection.Close
Set sqlConnection = Nothing

End Function

Open in new window

Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

Commented:
Select * From    Table
Select @@ROWCOUNT        
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

Commented:
Select *,(Select @@ROWCOUNT )
From    INFORMATION_SCHEMA.ROUTINES
Where   Routine_Type = 'Procedure' and
        Routine_Definition Like '%Order_Details%'
       

Author

Commented:
Patel, please forgive my ignorance here. But, is this an example of vba code or sql server code?

thanks,
Anthony

p.s.
i will try to incorporate this on when I go back to work on Monday. Thanks again.
Leigh PurvisDatabase Developer

Commented:
I have absolutely no idea why I got a notification for this question (perhaps I commented at the time but then deleted it... can't recall).

However, reading the guidelines for objecting:
>> If there is a valid solution, please OBJECT and indicate the comments that are, or would otherwise lead to, a solution

Anthony Perkins' solution is everything required to satisfy the question asked.
https:#a35758523

Cheers

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial