michd
asked on
Returning Record Counts SQL Server Stored Procedure
SQL Rookie here...
I am trying to return a Count from a SQL stored Procedure. When I run it returns the value that is assigned in the .Parameters("@ReportCount" ) = x statement. When I enter the passed parameters directly into the SQL it pulls back a count. I believe that it works, but not passing the parameters. In the .Parameters.Append .CreateParameter("@ReportC ount", 2, 3, 5) statement, I have tried both 2 and 3 as the next to last parameter for Output and Input/Output. 2 returns a null value. 3 does not change that value from what I passed in.
I am trying to return a Count from a SQL stored Procedure. When I run it returns the value that is assigned in the .Parameters("@ReportCount"
USE [ORS]
GO
/****** Object: StoredProcedure [dbo].[sp_SelectTransactionRange] Script Date: 02/18/2011 08:14:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_SelectTransactionRange]
@FromDate varchar(10),
@ToDate varchar(10),
@JOBNAME varchar(10),
@JOBNAME2 varchar(10),
@USERDATA varchar(10),
@SPLFNAME varchar(10),
@Frequency varchar(1),
@ReportCount int Output
AS
SELECT @ReportCount = COUNT(*)
FROM dbo.Transactions
WHERE JOBNAME=@JOBNAME and JOBNAME2=@JOBNAME2 and USERDATA=@USERDATA and SPLFNAME=@SPLFNAME and Frequency=@Frequency and [Transaction Date]>= @FromDate and [Transaction Date] <= @ToDate
GROUP BY JOBNAME, JOBNAME2, USERDATA, SPLFNAME, Frequency
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conTransactions
with cmd
.CommandText = "dbo.sp_SelectTransactionRange"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@FromDate", adVarChar, 1, 10)
.Parameters("@FromDate") = "01/01/2011"
.Parameters.Append .CreateParameter("@ToDate", adVarChar, 1, 10)
.Parameters("@ToDate") = "02/15/2011"
.Parameters.Append .CreateParameter("@JOBNAME", adVarChar, 1, 10)
.Parameters("@JOBNAME") = rsGetAS400("JOBNAME2")
.Parameters.Append .CreateParameter("@JOBNAME2", adVarChar, 1, 10)
.Parameters("@JOBNAME2") = rsGetAS400("JOBNAME2")
.Parameters.Append .CreateParameter("@USERDATA", adVarChar, 1, 10)
.Parameters("@USERDATA") = rsGetAS400("USERDATA")
.Parameters.Append .CreateParameter("@SPLFNAME", adVarChar, 1, 10)
.Parameters("@SPLFNAME") = rsGetAS400("USERDATA")
.Parameters.Append .CreateParameter("@Frequency", adVarChar, 1, 1)
.Parameters("@Frequency") = rsGetAS400("FTPFLAG")
.Parameters.Append .CreateParameter("@ReportCount", 2, 2, 1)
.Parameters("@ReportCount") = 0
.Execute
intReportCount = .Parameters("@ReportCount")
end with
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER