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
You can see i have set up a parameter called @rowcount. How do I set up my vba code to retrieve that value?
thanks!
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
You can see i have set up a parameter called @rowcount. How do I set up my vba code to retrieve that value?
thanks!
Last Comment
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_getRecIdsForProc
-- 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;
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_getRecIdsForProc
-- 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;
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
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
post your current vba code
I guess instead of
command.open/execute....
you just say
dim r as integer = command.executeScalar()
command.open/execute....
you just say
dim r as integer = command.executeScalar()
and of course you commant out the output parameter, you dont need that if you convert it to function...
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
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
you don't define the parameters correctly,
please read the article I linked above, it shows how to do.
please read the article I linked above, it shows how to do.
comment out these lines
Set this_rst = New ADODB.Recordset
and this
.Parameters("@rowcount").V
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...
Set this_rst = New ADODB.Recordset
and this
.Parameters("@rowcount").V
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...
ASKER
HainKurt,
I can do this without modifying more stored procedure?
thanks!
I can do this without modifying more stored procedure?
thanks!
ASKER
HainKurt,
vba does not like this dim rn as integer = getRecordSet.ExecuteScalar
vba does not like this dim rn as integer = getRecordSet.ExecuteScalar
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Select * From Table
Select @@ROWCOUNT
Select @@ROWCOUNT
Select *,(Select @@ROWCOUNT )
From INFORMATION_SCHEMA.ROUTINE
Where Routine_Type = 'Procedure' and
Routine_Definition Like '%Order_Details%'
From INFORMATION_SCHEMA.ROUTINE
Where Routine_Type = 'Procedure' and
Routine_Definition Like '%Order_Details%'
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.
thanks,
Anthony
p.s.
i will try to incorporate this on when I go back to work on Monday. Thanks again.
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
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
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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