asked on

return @rowcount from stored procedure

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!

Guy Hengel [angelIII / a3]

>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

HainKurt

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;

Anthony Berenguel

ASKER

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

HainKurt

post your current vba code

HainKurt

I guess instead of

command.open/execute....

you just say

dim r as integer = command.executeScalar()

HainKurt

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

Anthony Berenguel

ASKER

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]

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

HainKurt

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...

Anthony Berenguel

ASKER

HainKurt,

I can do this without modifying more stored procedure?

thanks!

Anthony Berenguel

ASKER

HainKurt,

vba does not like this dim rn as integer = getRecordSet.ExecuteScalar()

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alpesh Patel

Select * From Table
Select @@ROWCOUNT

Alpesh Patel

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

Anthony Berenguel

ASKER

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 Purvis

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