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!
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!
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
>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
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
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;
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
and of course you commant out the output parameter, you dont need that if you convert it to function...
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
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...
Use somthing like the function below. You would then call it as follows:
Dim this_rst As Integer
this_rst = sp_getRecIdsForProcessing(
Dim this_rst As Integer
this_rst = sp_getRecIdsForProcessing(
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
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%'
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial